IF/AND/OR Assistance Needed!

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))

Best Answers

Answers

  • AravindGP
    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)

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

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

  • AravindGP
    AravindGP ✭✭✭✭✭
    Answer βœ“

    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))

    Thanks,

    Aravind

    Reach out for any help on licenses, configuration, or training

  • Sam McDonald
    Sam McDonald ✭
    edited 03/19/24 Answer βœ“

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!