SUMIF

Philip Arnfield

I have a sheet with 12,000 rows, I want to sum all the rows with a certain product ID, in the case below the product ID is LSCI30 so I use the formula =SUMIF([Item Code]:[Item Code], "LSCI30", Net:Net) and the result that is returned is $18,642.89. but if I then apply a filter to the

201211_Filters & Formula.pdf

sheet using the same product ID and sum the column using the "sum" function I get a completely different result. I have tried using a different product code "LL" and the same thing happens. What obvious thing am I missing here?

Philip Arnfield

Absolutely correct, thank you Kevin. I copied the filtered column to excel and it added to the same amount as my sheet summary formula.

Kevin St-Pierre

Hi Philip,

Based on the screen capture, I see the SUM formula in your filtered view is =SUM(Net:4585:Net9103).

My first guess would be that unless the Sort order was also modified so that all LSCI30 entries are grouped together, the SUM formula is actually calculating any row within that range (rows 4585 to 9103) whether or not they have have been hidden by the filter, which may include Item Codes other than LSCI30 in your calculation.

In this case, the Sheet Summary value would be the value to trust, as it looks at the whole range and not only rows 4585-9103, while also validating the Item Code value.

Philip Arnfield

Absolutely correct, thank you Kevin. I copied the filtered column to excel and it added to the same amount as my sheet summary formula.