Sorting Dates Column and calculating MTD, YTD

[Pasha81]

Hi guys,

What I'm looking for is good ole fashioned formula-help on this one, no fancy-macro required unless you're feelin' generous hah.

So Column 1 I've got dates, need to sort through that and calculate Year-to-date and Month-to-Date values. These are both Sums of the cells....
YTD = Sum of all cells with most recent yr, in this case 2007
MTD = Sum of all cells in Column B for most recent month, Feb2007 here.

I've listed the desired solution for YTD and MTD on the sheet as well

(I'm guessing the solution will have something to do with SUMPRODUCT?)
Thanks in advance, i know its gonna be peanuts for some of you pros here

[martindwilson]

try
=SUMPRODUCT(--(YEAR($A$2:$A$500)=YEAR(MAX($A$2:$A$500))),B2:B500)
and
=SUMPRODUCT(--(YEAR($A$2:$A$500)=YEAR(MAX($A$2:$A$500))),--(MONTH($A$2:$A$500)=MONTH(MAX($A$2:$A$500))),B2:B500)

[Pasha81]

Thanks alot Martin! Extremely Sorry about the delay in response. I just realised that I hadn't marked this thread as solved.
Your formula is just what I needed
Many Thanks

[DonkeyOte]

Given the data is sorted by date I don't think you really need to use SUMPRODUCT, you could achieve the same with SUMIF (& MAX etc...), eg:

MTD:
=SUMIF(A:A,">"&MAX(A:A)-DAY(MAX(A:A)),B:B)

YTD:
=SUMIF(A:A,">="&DATE(YEAR(MAX(A:A)),1,1),B:B)

[Pasha81]

YEs you are correct Don, your method works as well
Thanks, its easier to understand

[DonkeyOte]

If you want to get to grips with SUMPRODUCT see Bob Phillips' white paper on the function - linked in my sig., it's how I and many others I am sure learnt how to use it in the first instance.

Regards SUMIF vs SUMPRODUCT - the former is significantly more efficient than the latter given the way in which each are processed (the latter is equiv. to an Array formula just without need for CSE)