Help using OR in SUMIFS

nadiasmartsheet

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

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

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

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

bmcmichael48661

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 

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.