Sumifs with multiple criteria including one Multi-Select column

Sumifs with multiple criteria including one Multi-Select column

edited 04/18/20 in Formulas and Functions

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

  • Genevieve PGenevieve P admin
    Accepted Answer

    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!




  • 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!

Sign In or Register to comment.