Excel – How to create an Excel SUMIF formula that can compare different date formats

microsoft-excel-2003

For my source data I have a column of dates in the dd/mm/dddd format, and a column of values next to it.

eg

03/02/2014  5 
17/02/2014  4 
03/03/2014  5 
17/03/2014  4

On a summary sheet I have a row of cells with dates in the mmm-yy format.

eg

Apr-11        May-11        Jun-11

In the cell beneath each of these dates I need to enter the sum of all the sources values where the month matches the header.

I know I can compare the two date formats using:

MONTH(A1)=MONTH(D4)

And if the dates matched, I could use SUMIF like this:

=SUMIF(A1:A53, =D4, B1:B53)

And I believe that Excel 2007+ has SUMIFS that might help me, but unfortunately I'm required to do this on Excel 2003.

But I can't work out how to get the month comparison work inside the SUMIF function.

If this possible?

Best Answer

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)
Related Question