Sum all fruits in table

[vorannon]

Hi. I would like to sum all fruits from table like this:

quantity	fruit 1	fruit 2	fruit 3
4​	apple	plum
3​	pear	apple	strawberry
8​	apple
6​	strawberry

And the result would be:

apple	15​
plum	4​
pear	3​
strawberry	9​

If I use sumif I only get sum from column fruit 1

Thank you for your help

 

[steve the fish]

Something like:

=SUMPRODUCT(($B$2:$D$5=A8)*$A$2:$A$5)

where A8 contains your named fruit and A1:D5 contains the source table.

 

[vorannon]

Thank you