IF/AND/OR Assistance Needed!

Sam McDonald

I'm trying to pull up the number of product sales by Region. Region is a multi select column. t works perfect when there's only once instance of MEA - SA or MEA - ME but there's one instance where I have both MEA - ME and MEA - SA in the same cell. I'm struggling on how to combine all three options together. Essentially if Region has MEA - ME or MEA - SA or MEA-ME and MEA-SA.

This works perfect when Region has either one or the other:

=SUMIFS({Target # Sales}, {Month}, "January", {Region}, OR(@cell = "MEA - ME", @cell = "MEA - SA"), {Focus Products}, HAS(@cell, $[Focus Product]@row)

I tried this for all three instances but it then comes up as 0:

SUMIFS({Target # Sales}, {Month}, "January", {Region}, OR(AND(@cell = "MEA - ME", @cell = "MEA - SA")), {Focus Products}, HAS(@cell, $[Focus Product]@row))

AravindGP

Hi @Sam McDonald

You can try this formula instead

=SUMIFS({Target # Sales}, {Month}, "January", {Region}, OR(CONTAINS("MEA - ME", @cel), CONTAINS("MEA - SA", @cell)), {Focus Products}, HAS(@cell, $[Focus Product]@row))

Sam McDonald

Hi @AravindGP that works a treat! Thanks so much!! 😁

AravindGP

Hi @Sam McDonald

If I understand you correctly, you would like to get the total sales for MEA when you have MEA - ME and/or MEA - SA. Do you have other MEA values in your multi-select dropdown that should not be part of the calculation?

If not, you can use this formula.

=SUMIFS({Target # Sales}, {Month}, "January", {Region}, CONTAINS("MEA", @cel), {Focus Products}, HAS(@cell, $[Focus Product]@row)

Sam McDonald

Hi @AravindGP thanks so much but we also have EMEA as a Region so this doesn't work

AravindGP

Hi @Sam McDonald

You can try this formula instead

=SUMIFS({Target # Sales}, {Month}, "January", {Region}, OR(CONTAINS("MEA - ME", @cel), CONTAINS("MEA - SA", @cell)), {Focus Products}, HAS(@cell, $[Focus Product]@row))

Sam McDonald

Hi @AravindGP that works a treat! Thanks so much!! 😁