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?

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

Any ideas?

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

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?

At least works for me...

Possibly autocalculation disabled?

ASKER CERTIFIED SOLUTION

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 ")

=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 ")

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.

=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.

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