Revenue Rank & Date Calculations

**dlythgoe** · 03-04-2009, 10:49 AM

Hi all,

This is on the same project as I was discussing in this thread:

http://www.excelforum.com/excel-work...ml#post2052648

But a new problem!

I need to work out Revenue Rank & Year to Date calculations.

Consider a simple table:

| Partner Name | Year | Month | Revenue |
------------------------------------------
| John Smith | 2008 | Nov | 2000 |
| John Smith | 2008 | Dec | 2200 |
| John Smith | 2009 | Jan | 1898 |
| Mary Smith | 2008 | Nov | 1767 |
| Mary Smith | 2008 | Dec | 1867 |
| Mary Smith | 2009 | Jan | 1953 |
------------------------------------------
etc..etc...

I'm not using Pivot Tables since there are more complex issues around presentation which are preventing me doing this so are using good old formulas..

Revenue Rank is in reference to the Partner in this case. I need to be able to say John Smith is rank x out of xx by summing up his revenues for both:

a) one month
b) a range of 3 months back

How do I work this out? Especially the date calculations when I just have a year and month in separate fields?

Many thanks in advance!

**JBeaucaire** · 03-04-2009, 12:41 PM

Maybe something like this:

**dlythgoe** · 03-05-2009, 12:49 PM

Thanks for that - really useful

Just one more thing if you don't mind...

How do you place a range condition within a SUMPRODUCT().

e.g.

SUMPRODUCT((DataTbl[manager_name]=ManagerSelect) * (Condition depends on a IF statement) * (DataTbl[Revenue])

So the (Condition depends on a IF statement) would be IF(logical_condition, output TRUE condition, output FALSE condition)

The conditions that are output need to be date boundaries based on YearSelect and MonthSelect variables.

The whole aim of this is...

Allow user to pick a manager, month and year and then sum up the revenues to date from the financial year end (YTD - From 'Previous September' to 'MonthSelected') for that selected manager.

Hope this makes sense and you can help!!

Cheers

**JBeaucaire** · 03-05-2009, 01:37 PM

Actually, I thought THAT was what I showed you. Dissect this formula and that's what it does:

=SUMPRODUCT(--($A$2:$A$100=$G22),--($B$2:$B$100=YEAR(H$1)),--($C$2:$C$100=LEFT(H$1,3)),$D$2:$D$100)

That formula from cell H22 in the sample sheet, breaks down as:

=SUMPRODUCT(--(NameChosen),--(YearChosen),--(MonthChosen),Values)

So how does this not provide the syntax for what you've asked here? You can adjust the ranges to suit, use named ranges as in your question, and use whatever cells you've designed as your "filter", but it works the same way.

I apologize if I'm missing something obvious.

**dlythgoe** · 03-06-2009, 05:20 AM

Hiya,

That's all cool for selected month and year and summing up totals but it's the date calculation for YTD thats the problem.

The financial year end is August 31st.

So if the report was for Feb 2009, YTD would equal the sum of revenues for Sep 08, Nov 08, Dec 08, Jan 09, Feb 09.

I noticed you hard coded the dates in the table and summed them up cummulatively across the row.

For my presentation, I need to just output YTD value so my first intial logic thought was:

If date of report > Aug 31st in the same year
{

//Condition True means it's the same year and include all months back to September

} else {

//Condition False means it's the year before and include all months back to September

}

So waht I'm finding confusing is the date calculations between 2 fields of just year and month and summing the corrosponding revenues back to September.

Sorry if I'm missing a clue from your spreadsheet...

Really appreciate your time though mate :D

**JBeaucaire** · 03-06-2009, 10:58 AM

<chuckle> Your original post asked for a month sum and a 3 months sum.

OK, YTD. Any chance you can just mock up the layout you're trying to accomplish? Or is the one I offered earlier sufficient to build on?