Help using OR in SUMIFS

I cannot figure out how to change this formula so it will add up amounts in the Stage Column that has a dropdown with several options. 

Right now, the below works, but I want it to work when the Stage is Verbal Commitment OR Written Commitment.

=SUMIFS([Committed $]9:[Committed $]64, [Current Campaign Name]9:[Current Campaign Name]64, "USAID match", Stage9:Stage64, "Verbal Commitment")


I'm out of guesses as to how to add that in!



  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    You can use basic math functions to add two SUMIFS together. Because SUMIFS does not allow OR or AND filters in the functions, you will have to add the two sumifs together like this: 

    =SUMIFS([Committed $]9:[Committed $]64, [Current Campaign Name]9:[Current Campaign Name]64, "USAID match", Stage9:Stage64, "Verbal Commitment")+SUMIFS([Committed $]9:[Committed $]64, [Current Campaign Name]9:[Current Campaign Name]64, "USAID match", Stage9:Stage64, "Written Commitment")

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 06/21/18

    its easier to use a collect.

    =SUM(COLLECT([Committed $]9:[Committed $]64, [Current Campaign Name]9:[Current Campaign Name]64, "USAID Match", Stage9:Stage64, OR(@cell = "Verbal Commitment", @cell = "Written Commitment")))

    Collect doesn't struggle with the format/data type issues a lot of other formulas have.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    @Luke. That is an interesting approach I will have to remember that one. I like that it is able to use the OR/AND function. Thanks for the suggestion. 

  • Luke-


    I am currently using this formula:

    =COUNTIFS(RAID:RAID, "Risk", Disposition:Disposition, "Open", Disposition:Disposition, "Retest", Disposition:Disposition, "Post Go-Live")

    I also have a drop down menu in the disposition column and need to count anything that is open, retest, or post go live. 

    I tried to use the formula above, but did not have any luck. Any feedback?


  • L_123
    L_123 ✭✭✭✭✭✭

    =countifs(Raid:Raid,"Risk",Disposition:Disposition,or(@cell = "Open",@cell = "Retest",@cell = "Post Go-Live"))


    you can't reference a column more than once in a countifs. To get around that you need an or statement.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!