SUMIFS with two condition in the same column

LeAndre P

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,

Mike Wilday

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:

https://help.smartsheet.com/function/sumifs

Mike Wilday

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:

https://help.smartsheet.com/function/sumifs

LeAndre P

Thank you Mike that did it!!

Mike Wilday

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

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.

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

Thank you Genevieve P. that worked.

Mike Wilday

Thanks @Genevieve P.

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.

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

Beautiful thank you again!