Aug 25 2021 05:12 PM
hi,
i have a array in one cell, like this:
dog,cat,fish
and i have a table of prices, like this:
dog - 1
cat - 2
fish -3
i want to get a formula that sum all the array, 1+2+3=6.
i can split the array to many cells, and do lookup and sum, but i have long array...
help!! tY!
Aug 25 2021 07:41 PM
Who ever produced this data should be hit with the workbook.
=SUM(XLOOKUP(FILTERXML("<y><z>"&SUBSTITUTE(A1,",","</z><z>")&"</z></y>","//z"),$E$1:$E$3,$F$1:$F$3))
Aug 26 2021 02:50 AM
Aug 26 2021 03:12 AM
It's not my data, it's your data.
And if you think the formula does not work then please provide a sample workbook.
Aug 28 2021 11:18 PM
@Detlef Lewin hi,
its work excellent with Excel. but i need use it with Excel online, and the function 'FILTERXML' dosent work there. can u help my again? sorry.... ty
Aug 29 2021 05:10 AM
SolutionAug 29 2021 05:10 AM
SolutionAs variant
However not very reliable (fish will be found in fishmeal)