SUMIF gives wrong result (not in range)

DavidPonnet

New Member
Joined
Mar 16, 2017
Messages
23
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.

9
9
view.php
16ack0z.jpg


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

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
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,342
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
deleted
 
Last edited:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,600
Messages
5,838,291
Members
430,537
Latest member
Antonio11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top