Any idea on using OR function with SUMIFS?

Syed Muhafzal
Syed Muhafzal ✭✭✭✭✭
edited 07/21/21 in Formulas and Functions

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:

Best Answers

  • Jaykel Torres
    Jaykel Torres Employee
    Answer ✓

    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
    Syed Muhafzal ✭✭✭✭✭
    Answer ✓

    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

Answers

  • Jaykel Torres
    Jaykel Torres Employee
    Answer ✓

    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
    Syed Muhafzal ✭✭✭✭✭
    Answer ✓

    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
    ZachJones ✭✭
    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

  • Genevieve P.
    Genevieve P. Employee Admin

    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

  • thank you Genevieve

  • CLC
    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å
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • CLC
    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!