SUMIFS Formula with Does Not Contain

I am creating a number of data sheets that will be the data for various Dashboard objects.

I have a master data file that lists all customers, Status, Pipeline $, etc. (Sales Pipeline Data Sheet)

In my current sheet, I am trying to enter a formula that will sum the total Pipeline $ for each Customer, along with the condition that the Status does not contain Invoiced.

The formula I currently have is:

=SUMIFS({Sales Pipeline Data Sheet - Range 1}, {Sales Pipeline Data Sheet - Range 2}, Customer@row, {Sales Pipeline Data Sheet - Range 3}, "<>*Invoiced*")

I am getting values returned, but it is including the total pipeline of each customer and NOT excluding those who have Invoiced included in the Status column (Range 3)

Thanks!

Kate

Find more posts tagged with

Accepted answers

Paul Newcome

Try this...

=SUMIFS({Sales Pipeline Data Sheet - Range 1}, {Sales Pipeline Data Sheet - Range 2}, Customer@row, {Sales Pipeline Data Sheet - Range 3}, NOT(CONTAINS("Invoiced", @cell)))

All comments

Paul Newcome

Try this...

=SUMIFS({Sales Pipeline Data Sheet - Range 1}, {Sales Pipeline Data Sheet - Range 2}, Customer@row, {Sales Pipeline Data Sheet - Range 3}, NOT(CONTAINS("Invoiced", @cell)))

KGetchell

Thank you, Paul! For the "@cell" component, where would that come from? The Range 3 criterion range is where I want to lookup the "invoiced" value.

Paul Newcome

You would leave it exactly as is. "@cell" tells the formula to look in the previously established range on a cell by cell basis.

KGetchell

Success!!! THANK YOU!

Paul Newcome

Happy to help. 👍️

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.