SUMIF gives wrong result (not in range)

[DavidPonnet]

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 !!!

 

[steve the fish]

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]

deleted