Any idea on using OR function with SUMIFS?

Syed Muhafzal

Hi Smarties,

I understand we can use OR function with SUMIFS or COUNTIFS - I just couldn't figure out how. I am wondering if there is a solution to the problem below

• "OR can be used directly in a boolean column (checkbox, flag, star) or can be used within another function—such as IF, SUMIF, COUNTIF, SUMIFS, and COUNTIFS."

Problem Statement: Sum the children rows in the Numbers Column if either the Status is Green OR Completed is checked.

Example sheet below =

I could do SUMIFS, but not sure how to go around having the Status is Green OR Completed is Checked?

=SUMIFS(CHILDREN(Numbers@row), CHILDREN(Status@row), "Green", CHILDREN(Completed@row), 1)

Thanks as always folks!

Jaykel Torres

Hey @Syed Muhafzal,

Although it is possible to use the OR Function within SUMIFS or COUNTIFS Functions, this can only be used when evaluating multiple criteria against the same Column. If more than one Column is involved, you will likely need to create two SUMIFS or COUNTIFS Functions then add them together. I've created the example below to demonstrate this:

Formula (highlighted in yellow): =SUMIFS(Numbers:Numbers, Complete:Complete, 1, Status:Status, @cell <> "Green") + SUMIFS(Numbers:Numbers, Complete:Complete, 0, Status:Status, "Green")

• The first SUMIFS Function sums Numbers where Complete is Checked and the Status is not Green
• The second SUMIFS Function sums Numbers where Complete is Not Checked and the Status is Green.

I hope this helps!

Jaykel

Syed Muhafzal

Thanks @Jaykel T.

This definitely help - but I improvised the formula a bit to include children.

=SUMIFS(CHILDREN(Numbers@row), CHILDREN(Status@row), <>"Green", CHILDREN(Completed@row), 1) + SUMIFS(CHILDREN(Numbers@row), CHILDREN(Status@row), "Green", CHILDREN(Completed@row), 0)

Cheers mate.

Syed

Jaykel Torres

Hey @Syed Muhafzal,

Although it is possible to use the OR Function within SUMIFS or COUNTIFS Functions, this can only be used when evaluating multiple criteria against the same Column. If more than one Column is involved, you will likely need to create two SUMIFS or COUNTIFS Functions then add them together. I've created the example below to demonstrate this:

Formula (highlighted in yellow): =SUMIFS(Numbers:Numbers, Complete:Complete, 1, Status:Status, @cell <> "Green") + SUMIFS(Numbers:Numbers, Complete:Complete, 0, Status:Status, "Green")

• The first SUMIFS Function sums Numbers where Complete is Checked and the Status is not Green
• The second SUMIFS Function sums Numbers where Complete is Not Checked and the Status is Green.

I hope this helps!

Jaykel

Syed Muhafzal

Thanks @Jaykel T.

This definitely help - but I improvised the formula a bit to include children.

=SUMIFS(CHILDREN(Numbers@row), CHILDREN(Status@row), <>"Green", CHILDREN(Completed@row), 1) + SUMIFS(CHILDREN(Numbers@row), CHILDREN(Status@row), "Green", CHILDREN(Completed@row), 0)

Cheers mate.

Syed

ZachJones

Trying to do something similar. Sometimes we split our invoice payments across multiple accounts, and I want to total up all the tools I bought with 900875.

I want to Sum the invoice paid where the primary account code = 900875 and then sum if the secondary account code = 900875.

=SUMIFS({Amt_INC_PD}, {Approval}, "Approved", {Prim_Acct_Code}, "900875 - Admin IFR", {Descript}, "Tools/Supplies") + SUMIFS({Amt_INC_PD}, {Approval}, "Approved", {SecondAcctCode} = "900875 - Admin IFR", {Descript}, "Tools/Supplies")

Keep getting an error. It works ok only but not with the second sumif

Genevieve P.

Hi @ZachJones

It looks like your second SUMIFS is just missing a comma!

{SecondAcctCode} = "900875 - Admin IFR",

should be

{SecondAcctCode}, "900875 - Admin IFR",

Try:

=SUMIFS({Amt_INC_PD}, {Approval}, "Approved", {Prim_Acct_Code}, "900875 - Admin IFR", {Descript}, "Tools/Supplies") + SUMIFS({Amt_INC_PD}, {Approval}, "Approved", {SecondAcctCode}, "900875 - Admin IFR", {Descript}, "Tools/Supplies")

Cheers!

Genevieve

ZachJones

thank you Genevieve

CLC

I'm currently trying to use a very small simple =SUMIF with OR formula and I just can't figure out how to make it work. One cell with two variables, each of which being multiplied to give a dollar amount for two teams for different amounts. Here's what I've got so far......

=SUMIF(OR(CREW@row, "KLM", [Square Feet]@row*1963), (CREW@row"Rock City", [Square Feet]@row*11))

Can someone help me with this?

Andrée Starå

Hi @CLC

I hope you're well and safe!

What do you want the formula to do?

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

CLC

If the "CREW" name is "KLM", multiply by "square feet" 19.63, If the "CREW" name is "Rock City", multiply "square feet" by 11.

Genevieve P.

Hi @CLC

Instead of using a SUMIF formula, I would simply use a nested IF statement here. Try this:

=IF(CREW@row = "KLM", [Square Feet]@row * 1963, IF(CREW@row = "Rock City", [Square Feet]@row * 11))

If you have other scenarios we can add them in as well.

Cheers,

Genevieve