How to compute the number of occurrences of a string in the entire workbook?

Question

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! error.

What's wrong with my formula? How can I get this count?

Gary's Student · Accepted Answer · 2014-12-02T19:35:53.777

2

Here is the equivalent VBA code to count the number of cells containing at least one string

Sub StringCounter()
    Dim r As Range, s As String, i As Long
    s = "*string*"
    For Each sh In Sheets
        Set r = sh.Range("C2:C150")
        i = i + Application.WorksheetFunction.CountIf(r, s)
    Next sh
    MsgBox i
End Sub

EDIT#1:

Here is the code to count the number of strings It will count multiple string within a single cell:

Sub StringCounter_II_The_Sequel()
Dim r As Range, s As String, i As Long
Dim rr As Range, L As Long
s = "string"
L = Len(s)
For Each sh In Sheets
    Set r = sh.Range("C2:C150")
        For Each rr In r
            txt = rr.Text
            If InStr(txt, s) > 0 Then
                i = i + (Len(txt) - Len(Replace(txt, s, ""))) / L
            End If
        Next rr
Next sh
MsgBox i
End Sub

EDIT#2:

The math works as follows:

say there are three string in a cell and the number of characters in that cell is 100
the replace removes all three instances and the new length is 82
the subtract results in 18
the division results in 3 (because the word string is 6 characters long)

edited Dec 02 '14 at 19:35

answered Dec 02 '14 at 17:56

Gary's Student

19,266
6
25
39

Thanks Gary. But if *string* appears many times in a cell, it's counted just once ! why ? – mounaim Dec 02 '14 at 18:36
@mounaim See my **EDIT#1** – Gary's Student Dec 02 '14 at 18:52
Can you please explain the if clause ? – mounaim Dec 02 '14 at 19:03
@mounaim The **IF** makes sure there is at least one *string* in a cell before performing any time-consuming math with that cell. – Gary's Student Dec 02 '14 at 19:05
I was in fact talking about the inside statement sorry Gary – mounaim Dec 02 '14 at 19:08
Brilliant formula @Gary's I get it now ! thank you very much :) – mounaim Dec 02 '14 at 19:17
for your edit number 2 I think you must state that each string is 6 characters long – mounaim Dec 02 '14 at 19:23
@mounaim I added the explanation as per your request – Gary's Student Dec 02 '14 at 19:36

Raystafarian · Answer 2 · 2014-12-02T17:21:27.277

1

You need to define each range for each sheet e.g.

COUNTIF(Sheet1!C2:C150,"*string*")+COUNTIF(Sheet2!C2:C150,"*string*") ...

If you want to be clever, you can say put each sheet in the column A, and in column B use

=COUNTIF(INDIRECT("Sheet"&A1&"!A1:A5"),"*string*")

Or whatever fits your naming scheme. Then drag it down for each count and sum that.

edited Dec 02 '14 at 17:21

answered Dec 02 '14 at 17:15

Raystafarian

21,583
11
60
89

I've lot of sheets :/ isn't there a way to avoid the long formula ? – mounaim Dec 02 '14 at 17:17
@mounaim you could write a macro. Or have a reference column for each sheet, which I've added to the answer. – Raystafarian Dec 02 '14 at 17:23
I'll try to write a macro for this since I'm beginner in VBA – mounaim Dec 02 '14 at 17:24

score 1 · Answer 3 · edited Mar 20 '17 at 10:16

1

You can write a pretty straightforward user-defined function in VBA to do this. Paste the following code into a new module (for instructions on how to do this, see this post):

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
Dim wbcs As Long, rng As Range, addr As String
For Each s In Worksheets
    'Limit search area to specified range.
    addr = InRange.Address
    Set rng = s.Range(addr)
    'Keep a running tally of the number of instances of the word in the specified range on each sheet.
    wbcs = wbcs + Application.WorksheetFunction.CountIf(rng, "*" & SearchFor & "*")
Next s
WBCountString = wbcs
End Function

Once you have this code in a module, you can use the function from the worksheet. For example, to count the number of instances of potato in column C on all worksheets, you would use the following syntax.

=WBCountString("potato",C:C)

edited Mar 20 '17 at 10:16

Community

1

answered Dec 02 '14 at 17:53

Excellll

12,627
11
51
78

I'm having an error, When I put the function in some new sheet with C:C range, Excel circumvent the C column of the active worksheet and returns just 0, I think some referencing problem is happening here – mounaim Dec 02 '14 at 18:31
@mounaim You can't put the formula in the C column or else it will introduce a circular reference error. Your options are either to refer to a part of the C column instead (e.g., `C1:C20`) and put your formula outside that range, or just put your formula somewhere else on the sheet. – Excellll Dec 02 '14 at 18:50
OK I get it now and I have corrected the error :) just one remark, if my string appears twice in a cell, it's computed only once :) why ? – mounaim Dec 02 '14 at 18:53
@mounaim I'm using COUNTIF, which counts cells. I (wrongly) assumed from what you had tried that counting cells would be sufficient. – Excellll Dec 02 '14 at 18:55
Oh I see ! may be I didn't mention it clearly, but this is what I wanted to do in fact – mounaim Dec 02 '14 at 18:59

How to compute the number of occurrences of a string in the entire workbook?

3 Answers3