Help with SUMIFS in a sheet Summary Field

patricks

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

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.

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!