Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

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.

Tags:

Comments

  • TJ (Tracie) Peters
    edited 09/01/17

    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

    Sample SUMIFS.jpg

  • Mike Wilday
    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? 

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

This discussion has been closed.