Sumifs with multiple criteria including one Multi-Select column
Hello - I am trying to sum a grant $ amount when one column is marked "Yes" to COVID-19 Related and the other column is either marked "Approve" or "Approve-Repurpose". I can't seem to figure it out. Can someone help?
=SUMIFS([Grant $]6:[Grant $]350, [COVID-19 Related?]6:[COVID-19 Related?]350, "Yes" AND([Approve/Repurpose/ Decline]6:[Approve/Repurpose/ Decline]350, (@cell) = "Approve", (@cell) = "Approve-Repurpose")
Thank you!
Best Answers
-
Hi @Volson
You've almost got it! Try using an OR instead of an AND, and placing it before your @cell & criteria but after the range, like so:
=SUMIFS([Grant $]6:[Grant $]350, [COVID-19 Related?]6:[COVID-19 Related?]350, "Yes", [Approve/Repurpose/ Decline]6:[Approve/Repurpose/ Decline]350, OR(@cell = "Approve", @cell = "Approve-Repurpose"))
You noted that there's a multi-select column. Which column would be multi-select, and would any of these values be present with other selections? If so, we will likely need to add in a HAS criteria as well so it can look to see if the cell HAS a certain value, instead of if the cell equals just one value.
Let me know if this works for you, or if you have any questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Wonderful! So glad it worked for you!
The HAS function would only be if your cell had both "Approved" & "Repurposed" selected in the same cell - technically this wouldn't be read in the formula as "Approved" because it has 2 values, not just the one. We would use HAS to make sure it would recognize that one of the selections is contained in that cell and is actually what we're looking for...
But since you only have one selection in the cell at a time, then this current formula will work fine! Yes, you are good without.
If anything calculates unexpectedly though, or if you need help adjusting this formula to SUM something else, let me know and I'm happy to help. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi @Volson
You've almost got it! Try using an OR instead of an AND, and placing it before your @cell & criteria but after the range, like so:
=SUMIFS([Grant $]6:[Grant $]350, [COVID-19 Related?]6:[COVID-19 Related?]350, "Yes", [Approve/Repurpose/ Decline]6:[Approve/Repurpose/ Decline]350, OR(@cell = "Approve", @cell = "Approve-Repurpose"))
You noted that there's a multi-select column. Which column would be multi-select, and would any of these values be present with other selections? If so, we will likely need to add in a HAS criteria as well so it can look to see if the cell HAS a certain value, instead of if the cell equals just one value.
Let me know if this works for you, or if you have any questions!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
It worked! Thank you so much! I had tried so many different options and was probably so close everytime. The multi-select column is the Approve/Repurpose/Decline one. It would only be one of the three. I need to calculate the grants that are COVID-19 related and that have been approved or repurposed. My inital calc was gathering all COVID-19 related grants as a forecast, but I needed this to show actuals once we have approved or repurposed. I am not 100% clear on your question with HAS. I think I am good without, but let me know what you think.
Thank you again Genevieve!
-
Wonderful! So glad it worked for you!
The HAS function would only be if your cell had both "Approved" & "Repurposed" selected in the same cell - technically this wouldn't be read in the formula as "Approved" because it has 2 values, not just the one. We would use HAS to make sure it would recognize that one of the selections is contained in that cell and is actually what we're looking for...
But since you only have one selection in the cell at a time, then this current formula will work fine! Yes, you are good without.
If anything calculates unexpectedly though, or if you need help adjusting this formula to SUM something else, let me know and I'm happy to help. 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!