Dynamic month and year criteria in Sumifs formula

Hi,

Please help me with Dynamic Sumifs formula.

Requirement: The below data set contains spend for different years and all I want the Sumifs formula to do is give me the current month and current year spend against each individual so for example we are in March 2016 so from the attached data set the result should be like.

Result: Colin $467,731 similarly as the month progresses the sumifs formula should show the current month and current year spend for each individual for example when file is opened in April the formula should show April 2016 spend for each individual. I have tried month(now()) and year(now()) but it didn't work. Appreciate your assistance

Name Date spend
colin 10-07-2015 $521,888
David 09-30-2015 $798,174
Mary 02-11-2015 $73,724
John 12-05-2014 $89,495
colin 03-20-2016 $185,742
David 04-02-2015 $43,370
Mary 07-14-2015 $73,724
John 10-07-2015 $89,495
David 09-30-2015 $185,742
colin 03-26-2016 $281,989

Thanks!
4gurus

Enter formula in F3 and copy down

Formula:

Please Login or Register  to view this content.

Hello my India friend,

Why don't you do a Pivot Table and filter it by this month and year. NO formulas needed. See the attached for my example.
PT for current Month and Year.xlsx

I think a year must be included too..

Formula:

Please Login or Register  to view this content.

4gurus,

Sorry you had problems.

I can only think it is the date format in Col B on your machine?

As you can see on the revised attachment, I changed all the Col B dates to 2016, and the values in Cols F & G changed automatically.

I even put a "Checksum" in C12 adding up all the rows, and it matches the Year to Date value in G8.

Hopefully it will work when you "play" with the dates in Col B, including putting some in previous years?

One quick point, though - if you set any of the dates to later than today, the formula won't count them, because it only counts anything up to Today's date in F2

Shout if it still goes wrong

Ochimus