Sumifs with multiple criteria including one Multi-Select column

Valerie Olson

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!

Genevieve P.

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

Genevieve P.

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

Genevieve P.

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

Valerie Olson

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!

Genevieve P.

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