0

I have to create a function that counts and shows in one cellm the times that the "s" character appears in a row range in Excel. It has to be robust enough since many times the "s" character can appear with hundreds of combinations, so doing =COUNTIFS(C4:AG4;"s";C4:AG4;"sc"...) wouldn't work.

I decided to write =LEN(C3)-LEN(SUBSTITUTE(C3;"s";"")) and it is robust enough to only count the times that s is said in each cell. I could simply copy paste that function and change the argument to D4,F4,G4,H4... and do a summation of it all, but I think it is not an elegant solution. Anyone has a better idea?

enter image description here

1 Answer 1

1

Use SUMPRODUCT() it will iterate through the row and count each cell and add them together.

=SUMPRODUCT(LEN(C3:AG3)-LEN(SUBSTITUTE(C3:AG3;"s";"")))

Or if you have Office 365:

=LEN(CONCAT(C3:AG3))-LEN(SUBSTITUTE(CONCAT(C3:AG3);"s";""))
2
  • Woah that works great, I didn't know about the SUMPRODUCT function, it will surely come in handy in the future, thank you very much.
    – Marco
    Feb 8, 2018 at 20:17
  • @Marco if this worked for you please consider marking as correct by clicking the check mark by the answer. Feb 8, 2018 at 20:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .