How to create a list of values in a cell to be treated by sumifS()?

[intmail01]

Hi,

Is it possible to treat a list of key values inside a cell that sumifS() formula will do iteration for each element ?
For example I have a list of keys (“A” , “B” , “C”) then sumifS() will treat A then B then add both result and continues with C and so on.
I have a huge table with a sring of search keys in each cell and it will take time to split manually each cell in several lines.

Thanks

[mikekaganski]

SUMIFS supports regular expressions → you may simply do

=SUMIFS(B$2:B$6;A$2:A$6;"A|B|C")

Just make sure to enable regular expressions in the calculation options.

[intmail01]

The list is a string in a hundred of cell then I can not type it directly in the formula.

[mikekaganski]

=SUMIFS(B$2:B$6;A$2:A$6;TEXTJOIN("|";1;<RANGE_WITH_SEARCH_VALUES>))

[intmail01]

This doesnt work. Look both pics. One is shows formula.

[mikekaganski]

… and did you enable regular expressions?

[intmail01]

It works now. I activate regular expressions option. Thank you.

[Villeroy]

Another approach:
ask112449.ods (22.1 KB)