Solved sumifs not returning correct answer

June 23, 2017 at 12:12:22
Specs: Windows 8
I have a large spreadsheet of data (30K+ lines) and want to be able to sum values based on the date. I have verified that all the cells that I am referring to actually are dates and not data strings (as suggested on this page), but my formula returns an answer of zero every time rather than the correct amount. Not an error - just zero.

My date field to evaluate is in column A (short date format) and the data I want to sum is column O. I have setup month-end dates in column AJ and my formula is: =sumifs($o$5:$o$30219,$a$5:$a$30219,">$aj$5",$a$5:$a$30219"<=$aj$6") where aj5 contains the last day of the month prior to the month that I want to sum and aj6 contains the last day of the month that I want to sum. My data consists of dates from 1/1/14 to the present and AJ5=12/31/2013 and AJ6=1/31/14. What this sample formula is trying to do is sum the data for January 2014.

I have tested the formula by eliminating the second set of criteria (at which point the formula SHOULD have summed everything on my spreadsheet in column O since all the data in the spreadsheet is after 12/31/13. But still the formula returns an answer of Zero. What am I doing wrong?


See More: sumifs not returning correct answer

Reply ↓  Report •

✔ Best Answer
June 23, 2017 at 15:34:42
I take it that you did not simply copy my suggestion from my post into your spreadsheet. Maybe you should try that.

re: The quotation marks: "MS Office indicated that it was required when using <,>,or ="

Yes, the quotation marks are required around the operators but can't be used around the cell references themselves. Look carefully at my suggested formula and notice these parts. Notice what is within the quotation marks and what is not.

">="&$AJ$5
"<="&$AJ$6

The operators are within the quotation marks but the cell references are not. The cell references are concatenated (combined) with the operators using the &. If you put the cell references within the quotes, Excel will see them as Text, not as cell references.

Here's a working example...

I started with the following data. I would expect the SUMIFS formula to return 4, since I have 4 dates in January and a "1" in the corresponding rows in Column O.


         A              O                  AJ
5    1/1/2014		1		1/1/2014
6    1/5/2014		1		1/31/2014
7    3/1/2014		3
8    4/1/2014		4
9    1/19/2014		1
10   5/3/2014		5
11   1/23/2014          1

I used this formula:

=SUMIFS($O$5:$O$30219,$A$5:$A$30219,">="&$AJ$5,$A$5:$A$30219,"<="&$AJ$6)

It returns 4.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 23, 2017 at 12:35:15
It always troubles me when I see lower case letters in a posted formula. That tells me that it wasn't copied directly from Excel, so I can't really be sure that it is the formula being used.

In this case, your posted formula is missing a comma, so you should be getting an error (I do) not a 0 result.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#2
June 23, 2017 at 12:58:10
Try this...

=SUMIFS($O$5:$O$30219,$A$5:$A$30219,">="&$AJ$5,$A$5:$A$30219,"<="&$AJ$6)

1 - You can't put the criteria cell references inside the criteria quotes. Excel won't recognize them as cell references.

2 - >AJ5 will not consider 1/1/2014 as within your criteria range. If you want to "sum the data for January 2014" you need to use >=AJ5

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#3
June 23, 2017 at 13:12:04
=SUMIFS($O$5:$O$30219,$A$5:$A$30219,">aj5",$A$5:$A$30219,"<=aj6")
copied from excel for the original formula to obtain sum of January 2014 data

=SUMIFS($O$5:$O$30219,$A$5:$A$30219,">aj5")
copied from excel for the "test" formula just set for one criteria (all data after 12/31/13) - which should have been the sum of all 30K+ lines in column O


Reply ↓  Report •

Related Solutions

#4
June 23, 2017 at 13:13:15
sorry - the entry box to post original questions is so small, and wraps around so frequently that it is hard to see if you dropped a comma!

Reply ↓  Report •

#5
June 23, 2017 at 13:19:08
I didn't put the quotation marks around the AJ5 and AJ6 references - EXCEL did in the formula box as I entered it. MS Office indicated that it was required when using <,>,or =

I just tried entering the formula without the quote marks around the >aj5 and the <=ag6 and it pops up with an error message telling me that my formula contains an error


Reply ↓  Report •

#6
June 23, 2017 at 15:34:42
✔ Best Answer
I take it that you did not simply copy my suggestion from my post into your spreadsheet. Maybe you should try that.

re: The quotation marks: "MS Office indicated that it was required when using <,>,or ="

Yes, the quotation marks are required around the operators but can't be used around the cell references themselves. Look carefully at my suggested formula and notice these parts. Notice what is within the quotation marks and what is not.

">="&$AJ$5
"<="&$AJ$6

The operators are within the quotation marks but the cell references are not. The cell references are concatenated (combined) with the operators using the &. If you put the cell references within the quotes, Excel will see them as Text, not as cell references.

Here's a working example...

I started with the following data. I would expect the SUMIFS formula to return 4, since I have 4 dates in January and a "1" in the corresponding rows in Column O.


         A              O                  AJ
5    1/1/2014		1		1/1/2014
6    1/5/2014		1		1/31/2014
7    3/1/2014		3
8    4/1/2014		4
9    1/19/2014		1
10   5/3/2014		5
11   1/23/2014          1

I used this formula:

=SUMIFS($O$5:$O$30219,$A$5:$A$30219,">="&$AJ$5,$A$5:$A$30219,"<="&$AJ$6)

It returns 4.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#7
June 23, 2017 at 15:54:37
Thanks so much - works like a charm!

Reply ↓  Report •

Ask Question