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

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?

Best Answer


  • Kevin St-Pierre
    Kevin St-Pierre ✭✭✭
    edited 12/11/20

    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
    Answer ✓

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!