I've looked around for answers regarding this, but am still a bit confused. I have a column A in Excel with dates in the format mm-yyyy (i.e. September 1014 is Sep-14). I want to add all the values in column H that fall in a particular month and year. So for example, I want to add all values that in Sep-2014. I'm trying to do this using SUMIF, but I don't know how to format the criteria. I have seen solutions advising to use an array...is there another, simpler way to do this? The dates are formatted in Excel and I have manually added them. I just don't want a large formula since I might be using this quite a bit in my spreadsheet and Excel tends to lag when file size is large on my computer.

Thank you

up vote 5 down vote accepted

Rather than a SUMIF with one criteria, use SUMIFS so you can specify two criteria.

So you simply need to sum everything in column H where the value in column A is equal to, or after 1st September, AND before 1st October.

=SUMIFS(H:H,A:A,">=01/09/2014",A:A,"<01/10/2014")

Another option is to use SUMPRODUCT and use MONTH and YEAR to establish conditions for inclusion in the sum.

=SUMPRODUCT((MONTH($A$1:$A$10)=9)*(YEAR($A$1:$A$10)=2014),$H$1:$H$10)

This checks for the month being September and the year being 2014.

  • Andi's method worked, but I'll keep this in mind. Thank you! – Alti Oct 13 '14 at 16:17

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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