Forum Discussion

Galina Kocheva's avatar
Galina Kocheva
Copper Contributor
Mar 05, 2018

SUMIFS formula for a multiple select dropdown

Hi All, I'm working on a weekly spend report and I've used the VBA code (found in other discussions) to get a multiple select dropdown for countries. So I can now select Austria, Spain, Italy or Germ...
Formulas & Functions
Need Help
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 05, 2018

Hi Galina,

 

You may use something like

=SUMPRODUCT(ISNUMBER(SEARCH(<country name>,<countries column>))*<values column>)

 

  • Galina Kocheva's avatar
    Galina Kocheva
    Copper Contributor
    Mar 07, 2018

    Hi Sergei, thank you so much for getting back to me!

     

    The formula returned a #value! and I think it's because the multiple select dropdown results in a list where the countries are separated by a comma (country 1, country 2, country 3 etc) within the same cell, so it is not finding it in the country column.

     

    Is there a formula that can look at a list of items and return 1 if in a column, or 0 if not? I think this would make the SUMPRODUCT work 

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 07, 2018

      Hi Galina,

       

      Perhaps I misunderstood your logic. What I mean that's calculation like this

      If that's not your case could you please attach short sample?

      • Galina Kocheva's avatar
        Galina Kocheva
        Copper Contributor
        Mar 07, 2018

        Hi Sergei,

         

        I see what you mean. I need it more or less the other way around, please see an example attached.

         

        Hope that makes more sense, I know it's a bit fiddly.

        Thank you for taking a look!

Resources