# Help using OR in SUMIFS

edited 12/09/19

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!

Tags:

• ✭✭✭✭✭✭

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")

• ✭✭✭✭✭✭
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.

• ✭✭✭✭✭✭

@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?

Branden

• ✭✭✭✭✭✭

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