SUMIFS date range problem

Hi everyone,

I'm currently creating a spreadsheet that calculates Payments and Expenses, on Excel 2013.

I am using a SUMIFS formula to calculate values within a data range (monthly) and matches another criteria, and it works for the majority of cells. However, it does not appear to work for some months such as April, June, September, November and February, and I cannot work out why.

The Excel spreadsheet is attached, and any help would be hugely helpful!

Cheers!

Simple personal cash flow statement1.xlsx

Your date format for those months (April, June etc.) in the end column are formatted DD/MM/YYYY whereas all the other dates are formatted as MM/DD/YYYY

Wait, that didn't change anything.

Do you know the rhyme: 30 days hath September, April, June and November, the rest have 31, except February, which has 28 ... except in a Leap Year when it has 29. OK, it's not much of a rhyme.

But, what do you notice about the months listed? Have a look at your table of end dates and fix it

Regards, TMS

I was just looking at that myself TMS. I'm not the OP, but I have no clue why it matters in this case.

Never mind, I figured it out, duh

You're welcome. Thanks for the rep.

Originally Posted by gmr4evr1

..... but I have no clue why it matters in this case.....

If you have a value in a cell like 31/02/2015 then Excel recognises that as a text value, not a date, so the SUMIFS doesn't work because those type of functions will only compare text to text and numbers to numbers

Just use this formula to get the last date of the month in cell C3 sheet Dates, then drag down:

=EOMONTH(B3,0)