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!
Comments
-
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")
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 55 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!