# Any idea on using OR function with SUMIFS?

Overachievers
edited 07/21/21

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!

Tags:

• Employee

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

• Overachievers

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

• Employee

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

• Overachievers

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

• edited 11/17/22

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

• Employee

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

{SecondAcctCode} = "900875 - Admin IFR",

should be

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

October 8 - 10, Seattle, WA | Register now

• thank you Genevieve

• ✭✭

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?

• ✭✭✭✭✭✭

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭

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

• Employee

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

October 8 - 10, Seattle, WA | Register now

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!