CODEWITHSUNDEEP
excelexcel-formula
weasel
weasel

Reputation: 574

excel SUMIF not giving right answer

I have a table with data and I am trying to do the following on another sheet. I am trying to sum the price of items in a column if the date is a particular date. I filtered all the dates using UNIQUE on the second sheet and doing a SUMIF based on those values. The problem is, the results are all slightly less than the actual value obtained by manual summation/ highlighting the relevant cells and seeing the sum on the bottom. Why is this happening? The discrepancy cannot be attributed to any single cell it is supposed to sum.

btw anyone knows why the similar questions box seems to be bugging out? The original text stays in place as I scroll, like some kind of ghost effect.

edit: here is a picture of the problem enter image description here

Upvotes: 0

Views: 1322

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

Here is what I think is the explanation.

From the documentation: The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using the upper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument

Your formula: =SUMIF(F2:F31,"="&H2,G:G)

thus evaluates to =SUMIF(F2:F31,"="&H2,G1:G30)

Because of the offset in the ranges, the last instance of range = H2 gets dropped. And 34.86-33.67 = 1.19 which is the last cell that should be added.

In other words, g1 is evaluated against f2, and g13 is evaluated against f14. Since f14 does not contain 3/2/2020, the 1.19 does not get added by SUMIF

Sometimes this is useful. But here, as you note, it has an unwanted result.

It works in your first case because both range and sum_range start in the same row.

All you need to do to get it working in your second case is to ensure both ranges start at the same row.

eg: =SUMIF(F2:F31,"="&H2,G2) would work

Upvotes: 1

Related Questions