Weird sum in excel when using SUMIF

0
votes

Can't understand why my formula sums up to this big of a number? The sum changes when changing the the range of the criteria. The criteria is blank, so I thought and hoped the sum would be 0?

Excel

1
excelexcel-formula
What are you trying to do here? What is the answer supposed to be and why? What are you looking for in Column H? - urdearboy
Why are you wrapping a SumProduct around a SumIf?? Looks like this is an XY Problem. meta.stackexchange.com/questions/66377/what-is-the-xy-problem - teylyn
In column H the plan is that I can write in Test1 and Test2, and the the sum would be 5. But since the range in SUMIF is the entire column H, this does not happend. - KevinElshaug
@teylyn If I dont the formula will sum one and one row. imgur.com/a/InEQFjm (Had to change the range so the spill could work) - KevinElshaug
I would add a column - enter 1 for those included and use sumproduct(). - Solar Mike

1 Answers

2
votes

The formulas are behaving correctly, what's weird is how you're using them. I can't think of a reason to use Sumif combined with Sumproduct (though somebody on here probably could).

To explain what's happening, you're creating a spill-range/array with your sumif formula because your middle parameter is a range (H:H), rather than a single value (such as test1). This is adding up all the values of the four cells with a value in column A (1+2+3+4=10). From there, it's spilling this into every row of excel, which is due to your middle parameter.

After that completes, the sumproduct function is summing all of these values, which is why you're seeing a value of 10,485,760 (the exact number of rows in Excel multiplied by 10).

If you're using sumproduct, you would want to use multiple array parameters. Your current formula only has one, which is no different than just using the sum value.