SUMIFS with OR function (multiple criteria in same column)

Options

Hello,

I am trying to get a sum if the criterion in a certain column meets one of several conditions. If the country code equals one of the country codes in the "country" column, I would like to sum the Total MVE for each country code. Right now I have"

=SUMIFS([Total MVE]1:[Total MVE]66, Country1:Country66, ="AU", Country1:Country66, ="BR", Country1:Country66, ="CA", Country1:Country66, ="CN", Country1:Country66, ="DE", Country1:Country66, ="RF", Country1:Country66, ="IL", Country1:Country66, ="IN", Country1:Country66, ="JP", Country1:Country66, ="KR", Country1:Country66, ="MX", Country1:Country66, ="RU", Country1:Country66, ="SG", Country1:Country66, ="TW", Country1:Country66, ="WO", Country1:Country66, ="FOREIGN: OTHER")

I'm getting $0.00 when I should be getting $4,500.

I think I'm missing the OR part of this function, but I can't figure out where it goes. So many arguments!

Can anyone help?



Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Kayla Q

    Try this

    =SUMIFS([Total MVE]1:[Total MVE]66, Country1:Country66, OR(@cell="AU", @cell ="BR", @cell ="CA", @cell ="CN", @cell="DE", @cell ="FR", @cell="IL", @cell ="IN", @cell="JP", @cell="KR", @cell ="MX", @cell ="RU", @cell="SG", @cell ="TW", @cell ="WO", @cell ="FOREIGN: OTHER"))

    I noticed that the OR includes all of the countries in the screenshot. If this is an unfiltered view, eg this is the entire list, the OR function is unnecessary.

    =SUMIFS([Total MVE]1:[Total MVE]66, Country1:Country66, <>""). In fact, you could use a SUM function and just SUM the range since you have designated the row numbers

    SUM([Total MVE]1:[Total MVE]66)

    Do any of these work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hey @Kayla Q

    Try this

    =SUMIFS([Total MVE]1:[Total MVE]66, Country1:Country66, OR(@cell="AU", @cell ="BR", @cell ="CA", @cell ="CN", @cell="DE", @cell ="FR", @cell="IL", @cell ="IN", @cell="JP", @cell="KR", @cell ="MX", @cell ="RU", @cell="SG", @cell ="TW", @cell ="WO", @cell ="FOREIGN: OTHER"))

    I noticed that the OR includes all of the countries in the screenshot. If this is an unfiltered view, eg this is the entire list, the OR function is unnecessary.

    =SUMIFS([Total MVE]1:[Total MVE]66, Country1:Country66, <>""). In fact, you could use a SUM function and just SUM the range since you have designated the row numbers

    SUM([Total MVE]1:[Total MVE]66)

    Do any of these work for you?

    Kelly

  • Kayla Q
    Kayla Q ✭✭✭✭✭
    Options

    @Kelly Moore INCREDIBLE! That worked!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!