Sumifs with multiple criteria including one MultiSelect column
Hello  I am trying to sum a grant $ amount when one column is marked "Yes" to COVID19 Related and the other column is either marked "Approve" or "ApproveRepurpose". I can't seem to figure it out. Can someone help?
=SUMIFS([Grant $]6:[Grant $]350, [COVID19 Related?]6:[COVID19 Related?]350, "Yes" AND([Approve/Repurpose/ Decline]6:[Approve/Repurpose/ Decline]350, (@cell) = "Approve", (@cell) = "ApproveRepurpose")
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, [COVID19 Related?]6:[COVID19 Related?]350, "Yes", [Approve/Repurpose/ Decline]6:[Approve/Repurpose/ Decline]350, OR(@cell = "Approve", @cell = "ApproveRepurpose"))
You noted that there's a multiselect column. Which column would be multiselect, 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
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, [COVID19 Related?]6:[COVID19 Related?]350, "Yes", [Approve/Repurpose/ Decline]6:[Approve/Repurpose/ Decline]350, OR(@cell = "Approve", @cell = "ApproveRepurpose"))
You noted that there's a multiselect column. Which column would be multiselect, 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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
It worked! Thank you so much! I had tried so many different options and was probably so close everytime. The multiselect column is the Approve/Repurpose/Decline one. It would only be one of the three. I need to calculate the grants that are COVID19 related and that have been approved or repurposed. My inital calc was gathering all COVID19 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. 🙂
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
Help Article Resources
Categories
Check out the Formula Handbook template!