Excluding item on a dropdown when returning sum in smartsheet

Samseen ✭✭
edited 01/07/21 in Formulas and Functions

Hello beautiful people,

On a second sheet, I need to pull in data from a first sheet.

Some columns on the first sheet contains the following:

- Service Type

- Commodity Category

- Invoice Amount

The Commodity Category column is a drop down of items, which includes "LLIN". I want to get the sum of the Invoice Amount of every other category excluding LLIN on a separate sheet.

Here's what I've tried among other formulas:

=IF(NOT({Commodity Category} = "LLIN"), SUMIFS({Invoice Amount}, {Service Type}, "Last-mile", {Subcontractor}, "Axios Foundation Ltd/Gte (Axi) Distribution"), 0)

=COUNTIFS({Commodity Category}, <> "LLIN", SUMIFS({Invoice Amount}, {Service Type}, "Last-mile", {Subcontractor}, "Axios Foundation Ltd/Gte (Axi) Distribution"))

=SUMIFS({Invoice Amount}, {Service Type}, "Last-mile", {Subcontractor}, "Axios Foundation Ltd/Gte (Axi) Distribution", NOT(CONTAINS("LLIN", {Commodity Category})

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Samseen

    You were close.

    Try something like this.

    =SUMIFS({Invoice Amount}, {Service Type}, "Last Mile", {Subcontractor}, "Axios Foundation Ltd/Gte (Axi) Distribution", {Commodity Category}, <>"LLIN")

    Did that work?

    I hope that helps!

    Have a fantastic day & Happy New Year!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!