3

I have a column with the following cell values:

ce
Km
st
Ge
eS
k
lm
LS
lt

I would like to count any cell that contains the letter "s", "t", "m", or "g" but I do not want to double count a cell that has a combination of those letters in it. For example, a cell that has "ts" I would like to count just once, instead of counting both the "s" and the "t".

Is it possible to do this with a formula?

I've tried this formula:

SUM(COUNTIF(G4:G12,{"*s*","*t*","*g*","*m*"}))

which returns 3 (I think it only counts anything with "s").

and this formula:

 "sumproduct(len($G$4:$G$29)
-len
(substitute
(SUBSTITUTE
(SUBSTITUTE
(SUBSTITUTE
(upper($G$4:$G$29)
,""T"",""""),""S"",""""),""G"",""""),""M"","""")))"

which returns 8 because it counts the "st" cell as 2.

3

A bit shorter:

=SUMPRODUCT(REGEXMATCH(A1:A9,"(?i)[stgm]"))
2

You fail to mention whether case sensitive, but assuming your list is in G4:G12 you might try:

=sum(ArrayFormula(--(len(G4:G12)<>len(regexreplace(G4:G12,"s|t|m|g","")))))

SUM
LEN
REGEXREPLACE

Prepend the second s with (?i) for case insensitive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.