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
    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 Community Champion

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

This discussion has been closed.