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 Germany, UK and any other possible combination out of around 40 different countries. However, my sumifs only work for one country or All and not when I select more than one.

 

Does anyone know a workaround the sumifs function to make it reference the list of countries listed with commas in the data validation dropdown?

 

Thanks!!

Formulas & Functions
Need Help

7 Replies

  • 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?

Resources