If you always have three letters in the cells in Row 1, you can use:
=SUM(--ISNUMBER(SEARCH(MID(B$1,{1,2,3},1),$A2)))
This is an incremental evolution of laurent’s answer. I made a couple of optimizations.
First of all,
IF (
Boolean_value
, 1 , 0)
is a little redundant, since TRUE has the value 1 and FALSE has the value 0.
Booleans can be readily converted to their equivalent integers with the --
prefix;
short for - ( - (
value
) )
, this preserves the numeric value
but coerces the type to integer.
And then the {1,2,3}
is an array that (with the SUM
function)
eliminates the need to add three nearly identical terms.
To handle strings in Row 1 with varying lengths, use
=SUM(--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))
This is like the first answer
except we get the length of the string at the top of the column, LEN(B$1)
,
and concatenate it to the string “1:
”, forming something like “1:3
”.
This looks like a range of rows, and the INDIRECT
function treats it exactly that way,
returning the address range $1:$3
.
And then ROW()
of that returns the array {1,2,3}
.
This is a trick to create an array of consecutive numbers
whose beginning and end are not predetermined.
The above formula must be entered with Ctrl+Shift+Enter.
I’m not sure why it must and the first one doesn’t.
Your example/illustration makes it look like your strings have embedded spaces.
If that’s true (but you don’t want the spaces to count), the formula becomes
=SUMPRODUCT(--(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1)<>" "),--ISNUMBER(SEARCH(MID(B$1,ROW(INDIRECT("1:"&LEN(B$1))),1),$A2)))
which also must be entered with Ctrl+Shift+Enter.