SUMIF gives wrong result (not in range)

DavidPonnet

New Member
Joined
Mar 16, 2017
Messages
21
when using sumif in this example, I get the wrong result. It even adds values from out of the range (see picture)

Cell B78 is never in the range, yet it adds it to the result of cell BL78

I have been trying to look for the error all day, and just found this, but can not explain this.



(the formula was originally from another sheet, but I changed the criteria to show the ranges. The results are the same for all things I try

Another thing, when I change the value of cell A78 to fe. "A", the result is correct, another mindbogling things I cant wrap my head around...

If ANY one could help me out, that would be great !!!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,112
Office Version
  1. 365
Platform
  1. Windows
Its just the way SUMIF works. See if this produces the same result (add sheet names first)

=SUMIF(A78:BM85,A78,BL78:DX85)

Try it with a SUMIFS and you will see it errors. It has to have the same number of cells to test as it has to sum if the test is true.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,192
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
deleted
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,543
Messages
5,529,456
Members
409,878
Latest member
DDhol
Top