I can't find a way to do this in less steps, so...
Assumptions
I'm assuming that on the back sheet, where we only see month-year, that the date is still stored as a date, and then formatted as a month (so underneath it actually says 01/01/2014, rather than 01-2014.
I'm also assuming that your main date is in column A.
The Issues
The main issue is that you need to be get your main dates into a reliable format (as in you need every date from say, May 2014, to be the same date,) so you can accuratelycompare them.
The first statement of a SUMIF
formula requires a range, and you can't run the kind of date transformations you need on a range. You may be able to do this with an array formula, but I've had no luck with it, and array formulas so esoteric you'll make it very hard for other users to understand.
Secondly, your currently using MONTH
to make the two values comparable. This will work, but it will end up summing up any instance of the month, from any year. You need to include the year to ensure that the result is accurate.
The Simplest Solution
I'm never a fan of solutions that rely on "helper columns" but for now at least it's necessary.
Create a column next to your main date column (this is your helper column, and you can hide it or Group hide it for normal use,) and in your first row cell add this formula:
=EOMONTH(A2,0)
EOMONTH
(End Of Month) will return the last day of the month for the target date. And the number after it allows you to find the last day of other months (put -1 in there and it will find the last day of the previous month)
Copy this formula down and now go to where you want the SUMIF
to go. In that cell enter this:
=SUMIF($B2:$B10,EOMONTH($D$1,0),$C2:$C10)
Where $B2:$B10
is the range of the helper column, $D$1
is the month value and $C2:$C10
is the range of the values to be summed.
Now repeat as needed.
EDIT
If you can't use EOMONTH due to not being able to the use the required Add On packs then you could use these formula instead.
=MONTH($A2)&YEAR($A2)
=SUMIF($B2:$B10,MONTH($D$1)&YEAR($D$1),$C2:$C10)
1
Have you tried doing this with Pivot Tables and use group by month? (http://www.databison.com/pivot-table-quick-tip-group-by-year-quarter-month-and-date-field/)
– shinjijai – 2014-02-04T14:14:03.553Pivot Tables are pretty much always the best way to go, though they do look mighty ugly in 2003 – CLockeWork – 2014-02-04T14:39:20.210
And as an aside; you don't need the
=
in the criteria statement. You have=D4
but justD4
is all you need :) – CLockeWork – 2014-02-04T14:43:00.467Whilst Pivot Tables have their uses, in the complex spreadsheet I'm having to work with they are not suitable. – Dave – 2014-02-05T10:04:23.637