# Excel Count Occurrences of String

I am trying to count the number of times a string such as "Climbing" occurs in any of cells N2 or X2 or AF2 or AN2 or AV2 or BD2 or BL2 or BT2 or CB2 or CJ2. The columns in-between would never contain "Climbing" so if it would be easier to just count occurrences of "Climbing" in the range N2:CJ2 that should work.

I tried COUNTIF(N2:CJ2, "Climbing"), which I thought should have counted correctly.  But I kept getting 0 no matter what.

Any ideas?
Peter Chan

Hi,
You need to scan through the relevant sheets to use instr to detect if those specific fields/columns are having "Climbing" inside.
witzph1

Looking for a formula for Excel.  I'm not sure what "instr" is, and there is only one sheet.
Hi,
You can use loop to scan the whole sheet. Read

https://www.educba.com/vba-while-loop/

Instr is the function to detect appearance of specific sub-string of given field/cell.
https://docs.microsoft.com/en-us/office/vba/Language/Reference/user-interface-help/instr-function

COUNTIF is the correct function....
At least works for me...
Possibly autocalculation disabled?

Subodh Tiwari (Neeraj)

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If a cell can contain multiple instances of Climbing then you can use (you must have office subscription and office insider for this formula)

=LET(st,"Climbing ",rg,N2:CJ2 ,SUM(LEN(rg)-LEN(SUBSTITUTE(rg,st,"")))/LEN(st))

If you don't have office subscription then you can try this ARRAY formula (You have to enter it using Ctrl-Shift-Enter)
=SUM(LEN(N2:CJ2 )-LEN(SUBSTITUTE(N2:CJ2 ,"Climbing ","")))/LEN("Climbing ")