Thread: Countif across sheets (Excel 97-sr2)

1. Countif across sheets (Excel 97-sr2)

I have a workbook with several identical sheets. I need to count the number of times a value appears in a particular column of any of the worksheets within the workbook. I have tried the following and get an error:

=COUNTIF(Sheet1:Sheet4!H:H,"ABC") where ABC is the value I want to count

Anyone have an idea?

2. Re: Countif across sheets (Excel 97-sr2)

<pre>=COUNTIF(Sheet1!H:H,"ABC")+COUNTIF(Sheet2!H:H ,"ABC")+COUNTIF(Sheet3!H:H,"ABC")+COUNTIF(Sheet4!H :H,"ABC")
</pre>

3. Re: Countif across sheets (Excel 97-sr2)

=COUNTIF doesn't work as a 3D function across sheets. (Neither do a lot of other Excel functions!) There may be some help here if you really need a 3d function:

<A target="_blank" HREF=http://www.j-walk.com/ss/excel/eee/eee003.txt>David Hager's EEE</A>

Otherwise, Legare's is the way to go.

4. Re: Countif across sheets (Excel 97-sr2)

Thanks. I was afraid that was the answer. My problem is I have 25 worksheets in the workbook and it is growing.

5. Re: Countif across sheets (Excel 97-sr2)

Hi,
If you want to do all sheets in the workbook, you could use a user-defined function like:
Public Function CountAcrossSheets(strColumn As String, strSearch As String) As Double
Dim dblCurrCount As Double, sht As Worksheet
Application.Volatile
dblCurrCount = 0
For Each sht In Sheets
dblCurrCount = dblCurrCount + Application.WorksheetFunction.CountIf(sht.Columns( strColumn), strSearch)
Next sht
CountAcrossSheets = dblCurrCount
End Function
so you would enter
=CountAcrossSheets("B","ABC")
to count all instances of "ABC" in column B on all worksheets (note: this is not case-sensitive)
Hope that helps.

6. Re: Countif across sheets (Excel 97-sr2)

That's what I am looking for!!

Thanks Much
<img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

7. Re: Countif across sheets (Excel 97-sr2)

You could use a function like this one:

<pre>Public Function CountIf3D(strStart As String, strEnd As String, strRange As String, vVal As Variant) As Long
Dim bFndStart As Boolean
Dim oSheet As Worksheet, oCell As Range
Dim lCount As Long
bFndStart = False
lCount = 0
For Each oSheet In ActiveWorkbook.Worksheets
If oSheet.Name = strStart Then
bFndStart = True
End If
If bFndStart Then
For Each oCell In oSheet.Range(strRange)
If oCell.Value = vVal Then
lCount = lCount + 1
End If
Next oCell
End If
If oSheet.Name = strEnd Then
CountIf3D = lCount
Exit Function
End If
Next oSheet
End Function
</pre>

You would use this function like this:

<pre>=CountIf3D("Sheet1","Sheet4","H:H","ABC")
</pre>

8. Re: Countif across sheets (Excel 97-sr2)

This was asked recently (on September 5th). See <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=71282&page=3&view =expanded&sb=5&o=0&fpart=>this thread</A>. It gives you another approach to count across sheets.

9. Re: Countif across sheets (Excel 97-sr2)

<P ID="edit" class=small>Edited by WebGenii on 12-Sep-01 12:09.</P>There is a way but it requires more than COUNTIF alone. I can't take credit for this formula. It came to me from another group I belong to. Let's assume you have 10 sheets in your workbook. The formula would be:

=SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(\$1:\$9)&"! B20"),"=1"))

I received this info from Microsoft News Groups at:
<A target="_blank" HREF=http://communities.microsoft.com/newsgroups/default.asp?icp=prod_office&slcid=us>http://communities.microsoft.com/newsgroup...ce&slcid=us</A>

Hope it works for you. It did for me.

Joe Nowak

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•