Help with SUMIFS in a sheet Summary Field

Hello!

I am stuck - trying to leverage a sheet summary field to sum a total, where one of the columns has two conditions that I want to exclude from the Sum. I can get one of them to work:

=SUMIFS([Amount]:[Amount], [Business Unit]:[Business Unit], "Sales", [Priority]:[Priority], <>"Required")

It's that last condition. I also want to exclude when the priority column also contains the text "NA".

But I can't figure out how to construct the formula to exclude both

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

Protonsponge

Hello @patricks,

It looks like in this image that the NA criteria is in a separate column, would that be correct?

Are you looking to sum [CapEx & OpEx], if [Finance Business Area] is "Data & Analytics" and [IT Priority] is not "1-Required in Flight" and [Strategic Alignment Activity] is not "NA"?

If so then I think the following could work for you.

All comments

Protonsponge

Hello @patricks,

The formula below could work for you, it will exclude "NA" and "Required" from the SUMIF formula.

=SUMIFS(Amount:Amount, [Business Unit]:[Business Unit], "sales", Priority:Priority, <>"Required", Priority:Priority, <>"NA")

I hope that is helpful to you in some way,

Protonsponge

patricks

Thanks for the speedy reply. Not quite there yet.

Here is the acutal column names - the amount doesn't change in the Summary field - so it is not excluding those two criterria:

=SUMIFS([CapEx & OpEx]:[CapEx & OpEx], [Finance Business Area]:[Finance Business Area], "Data & Analytics", [IT Priority]:[IT Priority], <>"NA", [IT Priority]:[IT Priority], <>"1-Required in Flight")

Protonsponge

Hello @patricks,

Are you set up like this image below, its functioning well in the demo.

=SUMIFS([CapEx & OpEx]:[CapEx & OpEx], [Finance Business Area]:[Finance Business Area], "Data & Analytics", [IT Priority]:[IT Priority], <>"NA", [IT Priority]:[IT Priority], <>"1-Required in Flight")

patricks

I am setup like that. The first column is a formula. The 2nd two are restricted dropdown lists. The data set shows that these items (marked with NA) are still being summed:

If I remove the 2nd condition, the summary is sitll including the NA items:

=SUMIFS([CapEx & OpEx]:[CapEx & OpEx], [Finance Business Area]:[Finance Business Area], "Data & Analytics", [IT Priority]:[IT Priority], <>"NA")

Makes me think it is the not equal nomenclature that isn't working.

Protonsponge

Hello @patricks,

It looks like in this image that the NA criteria is in a separate column, would that be correct?

Are you looking to sum [CapEx & OpEx], if [Finance Business Area] is "Data & Analytics" and [IT Priority] is not "1-Required in Flight" and [Strategic Alignment Activity] is not "NA"?

If so then I think the following could work for you.

patricks

That did it! Turns out it is what I call a stupid attack - I thought both of those values were in the IT Priority column! Thanks for the help and problem solved!

Protonsponge

Great! Really pleased you got sorted!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.