Sum multiple values from data validation list/ Excel 2013

=SUMPRODUCT(SUMIF(FRUIT;TRIM(MID(SUBSTITUTE(E3;",";REPT(" ";LEN(E3)));1+(ROW(INDIRECT("1:"&LEN(E3)-LEN(SUBSTITUTE(E3;",";""))+1))*LEN(E3))-LEN(E3);LEN(E3)));PRICE))

[princenkrumah]

Hello, can you help with a similar formula but assume I have 3 price lists which I want to choose using a drop down list. So the sum range should be able to change to a different column when I select that column in the drop down list.

[chrismontoya]

Great video. I keep getting the "theres a problem with this formula" when I try to change the ranges from FRUIT and PRICE in the sum formula to my own. I have the same set up with 2 columns of data. Any way to fix this?

[mountainlionbrian]

=SUMPRODUCT(SUMIF(FRUIT, TRIM(MID(SUBSTITUTE(E3, ", ", REPT(" ", LEN(E3))), 1+(ROW(INDIRECT("1:"&LEN(E3)-LEN(SUBSTITUTE(E3, ", ", ""))+1))*LEN(E3))-LEN(E3), LEN(E3))), PRICE))
for the example show. But for a generic use:
=SUMPRODUCT(SUMIF(A2:A13, TRIM(MID(SUBSTITUTE(C2, ", ", REPT(" ", LEN(C2))), 1+(ROW(INDIRECT("1:"&LEN(C2)-LEN(SUBSTITUTE(C2, ", ", ""))+1))*LEN(C2))-LEN(C2), LEN(C2))), B2:B13))

[danandmarlena]

I'm trying to do this same exact thing, but I can't figure out how to make the "FRUIT" list select multiple items like you did. 
Also, How do you make the items relational to the specific prices?
Thanks for any help!

[j-zone]

Hi, thanks for this tutorial. I would like to ask for a help. What would be the formula if I have few columns with drop down list of different values without repeat. And get a total sum on one column. Thank you.

[qawmysuri]

Dear Epp, PRICE RANGE is not being selected, only the fruit range is, whats the problem

[scout]

I am having issues with the function. I had to change all the : to, in order to get it to work but now all i get is a 0 value. Here is what I have: =SUMPRODUCT(SUMIF(Fruit, TRIM(MID(SUBSTITUTE(E3, ", ", REPT(" ", LEN(E3))), 1+(ROW(INDIRECT("1:"&LEN(E3)-LEN(SUBSTITUTE(E3, ", ", ""))+1))*LEN(E3))-LEN(E3), LEN(E3))), Price))

[elisabettaperessutti]

how can i sum it as if apple orange banana all singularly had a value of 1? so without moltiplying by price?

[saman]

Thank you this was very helpfull. I need you suggestion and will greatly appreciate your help I'm trying to make a liquidity tool in which I want to add 1000 when the liquidity goes negative at end of month, so, how can I use this drop down list in order to give option to add 1000 or not. As all the values will change.

[oakproff]

Hi Epp Holts

I got an Error when i use the this sumproduct function, for some reason it does not recognize my range value i think.

I have my own range and have rename the Fruit part.

Please guide

Thank you

[danieljohansson]

Something wrong with the formula? I can't make it work.