Forum Discussion
Sumifs return incorrect result
Hi Tech Community
I wonder if you can help me?
I have a spreadsheet which uses sumifs, but it seems that the results returned are not always correct.
I am not sure how to attach the spreadsheet to this post so here are some screen grabs.
It is odd because some results are correct and others are not.
The formula used is
=SUMIFS(Table2[Width],Table2[Plot],$B386,Table2[Head],$D$384)
The wide table at the top of the image is Table2 and consists of 377 rows by 20 columns.
If I reduce the table just to show plot 1-1 then the correct result is displayed, but it is also displayed for plot 1-23 which is not on the reduced table??
Here is the reduced table
And the results
Am I doing something wrong, should I try getting the results in another way that can be used in a different table?
Thnaks in advance.
Colin
- I'm guessing you didn't enter it as forced text (either '1-1 or into cells pre-formatted as text) and therefore Excel assumed those to be dates and in the case of 1-1 that is Jan 1 (this yr) and in the case of 1-23 it is Jan 2023 (and assumed the 1st) and hence BOTH 1-1 and 1-23 resulted in 2023-01-01 as the date value and hence the same value. but that is just my guess. In the other cases the Month-Day (of this year) and the Month-Year values didn't overlap so there wasn't an issue.
10 Replies
- After some tinkering around it would seem that SUMIF and SUMIFS did not like how the plot numbers were entered.
The cells were formatted as text and the notation was block number, dash, plot number (e.g. 1-1, 1-2, 1-3.....1.10, 1.11.....). Somehow this confused the formula and for example for plot 1-1 it was summing plots 1-1 and 2-23. Most other plots were summing correctly.
I have since changed the notation to block number, dot, plot number and placed a leading zero to plots under 10 (e.g. 1.01, 1.02......1.10,1.11.....).
SUMIFS seem to be happy with this and reporting the correct totals for each plot. It is odd as the cells are still formatted as text.
I am happy with this workaround but it would be nice to know why SUMIFS didn't like the dash notation.- I'm guessing you didn't enter it as forced text (either '1-1 or into cells pre-formatted as text) and therefore Excel assumed those to be dates and in the case of 1-1 that is Jan 1 (this yr) and in the case of 1-23 it is Jan 2023 (and assumed the 1st) and hence BOTH 1-1 and 1-23 resulted in 2023-01-01 as the date value and hence the same value. but that is just my guess. In the other cases the Month-Day (of this year) and the Month-Year values didn't overlap so there wasn't an issue.
The cells were preformatted as text, but not necessarily had the preceeding '.
Your date theory sounds plausible to me.
I have made a mental note not to use numbering scheme that could look like a date to Excel.
Thanks for your help.
With my previous answer I skipped that your message. Do you enter 1-1 as text ('1-1) or/and cells are formatted as texts?
Better if you share the formula for the cell in question.
And what is "reduced table" - you mean you physically removed some rows from the initial table? If you mean filtered table - filter doesn't affect SUMIFS().
The formula for the cell in question is
=SUMIFS(Table2[Width],Table2[Plot],$B386,Table2[Head],$D$384)
I reduced the table by using the resizing handle in the right hand lower corner.
It is not a filter but defines the size of the table.
