Sumifs with multiple criteria including one Multi-Select column

Valerie Olson
Valerie Olson ✭✭✭
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 P.
    Genevieve P. Employee Admin
    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!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    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!

    Cheers,

    Genevieve

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!