formula to exclude items

Raul Cabrera

I need to make a Sum of a column "Estimated Annual Revenue" but filtering by different options that I have in a column "Provide Client Sector", for example Commercial items and excluding from other column called "Status" items with "Cancelled", "Declined" and "Implementation complete"

I have this formula now working but still is missing the part of the exclusion on the column Status

=SUMIF({Client Sector}, [Client Sector]@row, {ROM Estimated})

Thanks

sharkasits

@Raul Cabrera Sorry about that I forgot to move the range to the front...

=SUMIFS({ROM Estimated}, {Client Sector}, [Client Sector]@row, {Status}, AND(NOT(CONTAINS(@cell, "Cancelled")), NOT(CONTAINS(@cell, "Declined")), NOT(CONTAINS(@cell, "Implementation complete"))))

Christian Graf

Hello @Raul Cabrera,

You're on the right track!

You'll want to use the SUMIFS() function for multiple criteria.

An example formula would be something like this:

=SUMIFS([ROM Estimated}, {Status}, <>"Cancelled", {Status}, <>"Declined", {Status}, <>"Implementation complete", {Client Sector}, [Client Sector]@row)

Hope this helps!

sharkasits

There may be a simpler way to do this, but if I'm understanding correctly this should work. SUMIFS allows you to have multiple criteria filtered on. For the multiple statuses you can use an AND.

=SUMIFS({Client Sector}, [Client Sector]@row, {Status}, AND(NOT(CONTAINS(@cell, "Cancelled")), NOT(CONTAINS(@cell, "Declined")), NOT(CONTAINS(@cell, "Implementation complete"))), {ROM Estimated})

Hope that helps!

Raul Cabrera

@sharkasits the formula send Incorrect Argument Set error

sharkasits

@Raul Cabrera Sorry about that I forgot to move the range to the front...

=SUMIFS({ROM Estimated}, {Client Sector}, [Client Sector]@row, {Status}, AND(NOT(CONTAINS(@cell, "Cancelled")), NOT(CONTAINS(@cell, "Declined")), NOT(CONTAINS(@cell, "Implementation complete"))))