How to use a cell's value (string) as part of a formula in Excel (not within a macro)?

Question

I need to sum a range of values in Excel based on one criterion.

So I want to sum say range B1:B10 based on values of range A1:A10 if the range A1:A10 answers to some criterion e.g. contains the words "apple - juice fresh" or "banana"

So the formula would look like :

=SUMPRODUCT(SUMIF(A1:A10;{"apple - juice fresh";"banana"};B1:B10))

This works as desired. Now, I don't want to have the list hardcoded in the formula. I want the list somewhere in a cell. Say cell C1. And so the cell C1 contains as a value :

{"apple - juice fresh";"banana"}

and I want the formula to be something like :

=SUMPRODUCT(SUMIF(A1:A10;C1;B1:B10))

But this does not work. How can I achieve this ? I tried CONCATENATE(C1) but it does not work.

Herebelow is a sample of what I would like

Have a look at FILTERXML() to break down the list into seperate elements. If you go this way, I'd stick to a single delimiter without the opening and closing brackets or the quotes. Another option using ISNUMBER() and FIND() combinations. Do you have a little bit of sample data to work with? Including your desired results. — JvdV
Oke, you got a nice answer down by Harun24HR. What version of Excel have you got? You are using SUMPRODUCT() so assume you won't have access to the new dynamic arrays? — JvdV
does the list need to be in one cell, not in a range? otherwise you can do =SUMPRODUCT(SUMIF(A1:A10,C1:C2,B1:B10)) where c1=apple and c2=banana — Jayvee

Harun24HR Harun24HR · Accepted Answer · 2021-05-07T07:47:14

You can try FILTER() then SUM() with Excel O365

=SUM(FILTER(B2:B8,ISNUMBER(SEARCH(A2:A8,D2))))

If you don't have O365 then try SUMPRODUCT() like

=SUMPRODUCT(--ISNUMBER(SEARCH(A2:A8,D2)),B2:B8)

Edit: If that is the case then go with FILTERXML(). Give a try on

=SUM(FILTER(B2:B8,ISNUMBER(XMATCH(A2:A8,FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"{",""),"}",""),CHAR(34),""),", ","</s><s>")&"</s></t>","//s"),0))))

How to use a cell's value (string) as part of a formula in Excel (not within a macro)?

1 Answers