How to use a formula for SUMIFS with more than 2 not contains criteria?

If I have 3 columns one is "department", the second is "description", and the third is "amount".

I would like to sum up the total "amount" for only department-1 in the "department" column, that doesn't contains "ABC" or "EFG" or "JKL" in the description column.

I am trying to use =SUMIFS({amount}, {department},{department-1},OR(NOT(CONTAINS("ABC", @cell),("EFG",@cell),("JKL",@cell)))}

My concern is with the NOT CONTAINS formula only. please help.

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Each piece of the OR function will be a separate NOT/CONTAINS.

OR(NOT(CONTAINS("ABC", @cell)), NOT(CONTAINS("EFG", @cell)))

phaze

I used this:

=SUMIFS({amount}, {department}, {department -1}, {description}, OR(NOT(CONTAINS("ABC", @cell)), NOT(CONTAINS("EFG", @cell)), NOT(CONTAINS("JKL", @cell))))

it summed up the total amount in the column without applying the "NOT CONTAIN" condition.

Paul Newcome

Apologies. Swap the OR function for an AND function.

"EFG" isn't "ABC", so it will still grab "EFG". If we switch to an AND function instead, it will exclude all three.

Sorry about that. I was just looking at the syntax and not necessarily the desired outcome.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.