Sumifs with multiple criteria including one Multi-Select column
![Valerie Olson](https://us.v-cdn.net/6031209/uploads/userpics/5PNT5VUH8Y0T/nLI6SAX24APWX.png)
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
-
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. 🙂
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
-
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. 🙂
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.2K Get Help
- 386 Global Discussions
- 212 Industry Talk
- 445 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 292 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!