SUMIFS with OR function (multiple criteria in same column)
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

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

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 INCREDIBLE! That worked!
Help Article Resources
Categories
Check out the Formula Handbook template!