SUMIFS not giving same result as regular old SUM'in

[Batstatgirl]

Hello! I'm trying to play with numbers (Excel 2010) from counting elk that crossed by a game camera. When using the SUMIFS function to categorize data by month (elk counted in February, March, April, May), the values given by my April and May formula are off by 1 compared to the value given by a SUM function with manually selected days. The formula I'm using is:

=SUMIFS(C3:C72,A3:A72,">=5-1-2014",A3:A72,"<=5-31-2014")

A column: dates (m-dd-yyyy)
C column: number of elk on that date

After some investigation, it seems that the SUMIFS formula isn't adding the data point from 5-31-14 (and 4-30-14, both of which have C column values of 1- the missing 1!!). February and March don't have data for the last day of the month, and the SUMIFS formula gives the correct numbers.

I just had a brain wave while writing this and switched the formula to:

=SUMIFS(C3:C72,A3:A72,">=5-1-2014",A3:A72,"<6-1-2014")

That also saves having to juggle numbers on Leap Years. Great success! On my way to Excel Wizard level.

But I'm still curious as to why the original formula is not inclusive of the final day of the month despite using "<=date". Any insights?

Thanks,
Kate

 

[Special-K99]

Try

=SUMIFS(C3:C72,A3:A72,">="&DATEVALUE("5-1-2014"),A3:A72,"<="&DATEVALUE("5-31-2014"))

 

[Jonmo1]

The dates in Column A are probably not just dates, but dates+time.

In the sumif, you've specified just the Date, not a time. So a time of Midnight is assumed.

So if your dates in column A include the time value, say 6 AM for example..
5-31-2014 @ 6AM is NOT less than or equal to 5-31-2014 @ Midnight
Therefor it is not counted in the formula.

 

[Batstatgirl]

Special-K99: Interestingly it gave the same value as my original formula, which was off by 1.
Thanks for getting back to me though!

 

[Batstatgirl]

The dates in Column A are probably not just dates, but dates+time.

Would this still hold true if I formatted the cells in Column A to just be a "date"?

Thanks for your help!

 

[Jonmo1]

Would this still hold true if I formatted the cells column to just be a "date"?

Yes

Formatting a cell only changes the way the value 'appears' in the cell.
It doesn't change the actual value in the cell.

You can see this by

Type =NOW() into any empty cell
It shows the date+time, right?
Copy that cell, and Paste special - Values
It still shows the date+time, right?
Now, format that cell as just mm/dd/yyyy
it shows just the date.
But if you select that cell and look at the Formula bar (not the cell), it still shows the date+time.

 

[Batstatgirl]

By god. It's all true.

Went back and removed all time from Column A, formula works like a dream.

Thanks so much!

 

[Jonmo1]

Thanks so much!

Glad to help, thanks for the feedback.

Went back and removed all time from Column A

You don't have to remove the times.
I was really only explaining why the original formula omitted the last day of the month.

Your solution of using less than the 'next' date is a perfectly logical and sound solution.
And is actually what I would have suggested if you had not already done it.

Also as you mentioned, it's easier to get the 1st day of the next month, than it is to get the last date of the current month (is it 28 29 30 or 31?)