1

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?

  • 1
    Have you tried doing this with Pivot Tables and use group by month? (databison.com/…) – shinjijai Feb 4 '14 at 14:14
  • Pivot Tables are pretty much always the best way to go, though they do look mighty ugly in 2003 – CLockeWork Feb 4 '14 at 14:39
  • And as an aside; you don't need the = in the criteria statement. You have =D4 but just D4 is all you need :) – CLockeWork Feb 4 '14 at 14:43
  • Whilst Pivot Tables have their uses, in the complex spreadsheet I'm having to work with they are not suitable. – Dave Feb 5 '14 at 10:04
1

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)
  • Your assumptions are correct. However, it would appear that using EOMONTH relies on the installation of the Analysis Helper Pak addin which I cannot guarantee would be installed on all the PCs that would access this file. (it isn't installed by default even with 'full' installation). However, if my only option is to use helper rows/columns then there are a few ways I could get around that. I was hoping not to have to go this route though, but it seems you may be right. – Dave Feb 5 '14 at 10:02
  • In place of EOMONTH I guess a concatenation of YEAR and MONTH would do. I'm still hoping someone else can think of a way of doing it without helper columns but it's not looking likely. You could do it with VBA, but then you have a similar issue with users and macro security. – CLockeWork Feb 5 '14 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.