Link to home
Start Free TrialLog in
Avatar of witzph1
witzph1

asked on

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?
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

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

ASKER

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


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

User generated image
ASKER CERTIFIED SOLUTION
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ")
Avatar of witzph1

ASKER

To all who contributed ideas, THANK YOU!!  I was able to find the solution most simply in Subodh's slight tweak of my COUNTIF formula which adds wildcards to the beginning and end of "Climbing."  As in:

=COUNTIF(N2:CJ2,"*Climbing*")

I probably was not clear enough in my initial description that the cells could contain more than just "Climbing," but by adding the wildcard it worked perfectly.

Saqib, in this instance each cell only could have Climbing once, making your formula not necessary.  But that is a very cool formula.  Thanks for sharing the idea.  I might keep for future needs.

Have a great day, all.