Forum Discussion

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

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

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
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!
- SergeiBaklan
  Diamond Contributor
  Mar 07, 2018
  Hi Galina,
  
  I see, that's in opposite way. When it could be
  
  =SUMPRODUCT(ISNUMBER(SEARCH($D$2:$D$6,$A2))*$E$2:$E$6)
  - Galina Kocheva
    Copper Contributor
    Mar 09, 2018
    Amazing, thank you so much, Sergei!!
    
    That has now worked for the multiple select dropdown.
    
    My next step is to incorporate other dropdowns too (so one for multiple countries, and one for a week, one for activity). I am working on a sumifs and a sumproduct formula incorporating what you've given me. Is this something we've seen before?
    
    Thanks
    
    Galina