Revenue Rank & Date Calculations

**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

Revenue Rank & Date Calculations

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Re: Revenue Rank & Date Calculations

Re: Revenue Rank & Date Calculations

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions