Excluding item on a dropdown when returning sum in smartsheet

Samseen

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})

Andrée Starå

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!

Best,

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å

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!

Best,

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!

Samseen

Thank you Andrée

Andrée Starå

@Samseen

You're more than welcome!