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

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";""))
  • 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 '18 at 20:17
  • @Marco if this worked for you please consider marking as correct by clicking the check mark by the answer. – Scott Craner Feb 8 '18 at 20:20

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.