SUMIFS with two condition in the same column

Hello,

I am having trouble creating a formula to trigger if 2 conditions are met within the same column. I am trying to sum the PO amounts for each time “Electrical” or “C Box” shows up in the CType column. Unfortunately, I am getting #UNPARSEABLE returned. 


This is the formulas I have tried so far:

=SUMIFS({CType}, OR(@cell = "Electrical", @cell = "Combiner Box") > 0, {PO Total}))


Thanks in advance,

Best Answer

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    With a sumifs you want to specify the range to sum first. From what I see you're trying to put it at the end. And you don't have to do any greater than 0's because you're looking for it in each cell. You also have to call the range for your criterion before you list it. Like below. Please let me know if this works for you.

    =SUMIFS({PO Total}, {Ctype}, OR(@cell = "ELECTRICAL", @cell = "COMBINER BOX"))

    For more on sumifs please see:


Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    With a sumifs you want to specify the range to sum first. From what I see you're trying to put it at the end. And you don't have to do any greater than 0's because you're looking for it in each cell. You also have to call the range for your criterion before you list it. Like below. Please let me know if this works for you.

    =SUMIFS({PO Total}, {Ctype}, OR(@cell = "ELECTRICAL", @cell = "COMBINER BOX"))

    For more on sumifs please see:


  • Thank you Mike that did it!!

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome! Please accept my answer. I am glad I could help you out.

  • Eyglo
    Eyglo ✭✭

    Hi there,

    I am trying to get similar formula working on my end, but I am running into the #Unparseable result.

    I would like the values in the Estimated DC kW column to be summed up if values in the Stage # column are either 1. Backlog or 0. Pipeline.

    Here is what I have tried:

    =SUMIFS([Estimated DC kW:Estimated DC kW], [Stage #:Stage #], OR(@cell=“0. Pipeline”,@cell = “1. Backlog"))

    Any insights would be highly appreciated.

    Best,

    Eyglo

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Eyglo

    It looks like your column references are off a little - you'll need to wrap [square brackets] around the column name each time, instead of around the column name listed twice.

    =SUMIFS([Estimated DC kW]:[Estimated DC kW], [Stage #]:[Stage #], OR(@cell = "0. Pipeline", @cell = "1. Backlog"))

    Additionally I noticed your quotes are curved. Ensure they're straight up and down "" like so!

    Cheers,

    Genevieve

  • Eyglo
    Eyglo ✭✭

    Thank you Genevieve P. that worked.

  • Eyglo
    Eyglo ✭✭

    I have one more question for the SUMIFS formula that I would appreciate any guidance on:

    Currently, I have 2 SUMIFS formulas for the same sheet to help me understand how much opportunity value the company has in total.

    The reason for that is that at a certain point in the project lifecycle we have better estimation of opportunity value so we use Estimated Value first and then populate an Actual Value when we have better sense of the project.

    THE GOAL is to have one value that represent the total opportunity value for the company.

    Do you have any suggestions on how I could merge these 2 formulas into 1 that is reading from 2 value columns:

    =SUMIFS([Estimated value]:[Estimated value], [Stage #]:[Stage #], OR(@cell = "0. Pipeline", @cell = "1. Backlog"))

    =SUMIFS([Actual value]:[Actual value], [Stage #]:[Stage #], OR(@cell = "8. R&D", @cell = "7. O&M", @cell = "6. Closeout", @cell = "5. Construction", @cell = "4. Pre-Construction", @cell = "9. Hold"))

    Any guidance is highly appreciated.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hey @Eyglo

    You can add two formula outputs together by using the + symbol 🙂

    =SUMIFS(...) + SUMIFS(....)

    This will give you a total. Try:

    =SUMIFS([Estimated value]:[Estimated value], [Stage #]:[Stage #], OR(@cell = "0. Pipeline", @cell = "1. Backlog")) + SUMIFS([Actual value]:[Actual value], [Stage #]:[Stage #], OR(@cell = "8. R&D", @cell = "7. O&M", @cell = "6. Closeout", @cell = "5. Construction", @cell = "4. Pre-Construction", @cell = "9. Hold"))


    Cheers,

    Genevieve

  • Eyglo
    Eyglo ✭✭

    Beautiful thank you again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!