Attempting to use SumIfs where 1 column could have multiple different values.

Phillip

Hello!

   I have tried to find the answer by browsing through questions already submitted about sumifs but I can't seem to find a question that addresses my issue.  Apologies if this has been asked and answered.  



I'm attempting to sum a column based on the values in 2 other columns.  



my column headers are: Sprint, Task Hours, and Stage.  

I'm attempting to sum Task hours where Sprint is equal to "Sprint 2" and Stage is: "Demo Ready", "Passed Demo", or "Deployed".

 

This is the formula I have so far



=SUMIFS([Task Hours]:[Task Hours], Sprint:Sprint, "Sprint 2", Stage:Stage, "Demo Ready")



Once I add another stage

=SUMIFS([Task Hours]:[Task Hours], Sprint:Sprint, "Sprint 2", Stage:Stage, "Demo Ready", Stage:Stage, "Deployed")

the formula returns 0.

I can do anyone of the Stage columns by themselves and a value is returned but once I try to add a 2nd or 3rd stage description the function returns 0.

I am assuming this is because when I write (Stage:Stage, "Deployed", Stage:Stage, "Demo Ready") the function is looked for a row that has "Deployed" and "Demo Ready".  

I have tried to use the OR function in conjunction with my Sumifs() but that doesn't seem  to work.  

 Any help would be greatly appreciated.

TJ (Tracie) Peters

Hi Phillip,

It's not text (AND, OR) but the addition that is the key, and it's two separate calculation strings. In effect, you are adding the first series of criteria (Sprint 2 and Demo Ready) to the second series of criteria (Sprint 2 and Deployed). You do this with the addition symbol +.

SUMIFS([Task Hours]:[Task Hours], Sprint:Sprint, "Sprint 2", Stage:Stage, "Deployed") + SUMIFS([Task Hours]:[Task Hours], Sprint:Sprint, "Sprint 2", Stage:Stage, "Demo Ready")

You are combining two possibilities into one. I have a screen shot added too. This was an interesting challenge and I hope it works for you!

TJ

Mike Wilday

Nice solution! Way to combine the 2 Sumif statements to get the intended result.

On another note, It seems like using OR("Deployed", "Demo Complete") should also produce results. I wonder if this is something that should be reported to Smartsheets? 

Phillip

TJ,

  Thank you very much the help! The explanation was very helpful.  The solution you provided works great!  



I was starting to think I would have to use multiple SumIfs but was thinking I would have to nest them.  I didn't realize that you could use '+' to add the results together.

 

Again thank you for the help!