SUMIFS with OR function (multiple criteria in same column)

Kayla Q

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?

Capture.PNG

Kelly Moore

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

Kelly Moore

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

@Kelly Moore INCREDIBLE! That worked!