Sumifs return incorrect result

[Colin_Padwick]

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

[#2]

@Colin_Padwick 

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().

[#3]

@SergeiBaklan 

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.

[#4]

@Colin_Padwick 

Thank you. One more question, are you in Automatic calculation mode?

[#5]

@SergeiBaklan 

You are welcome, yes, I am in automatic calculation mode.

[#6]

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.

[#7]

@Colin_Padwick 

I tried to play with different scenarios and can't reproduce, sorry. It looks like recalculation doesn't work in your case. You may try Ctrl+Alt+Shift+F9 if it updates the formula.

On that step if only to play with your file, if you may share it removing sensitive information, or someone else here have more ideas.

[#8]

@Colin_Padwick 

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?

[#9 · Accepted solution]

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]

@mtarler @SergeiBaklan 

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.

[#11]

@Colin_Padwick 

Excel considers as text any value if the cell is formatted as text and the value preceding with ' if any other format is applied to the cell.