# Thread: COUNTIF across sheets of a workbook (2000)

1. ## COUNTIF across sheets of a workbook (2000)

Hi,

I am trying to consolidate some data using an Excel workbook. It is in the form of a survey. People are being asked to place a value of 1-4 on each item. I am having difficulty in attempting to get the COUNTIF function to work. The listed format is COUNTIF(RANGE,CRITERIA). I can get this to work very nicely on a single sheet in a workbook but I cannot get it to work across several sheets in a workbook.

As an example:
I have 10 sheets with a question in A20. The response is placed in B20. I want the last sheet to count how many occurrences of 1, how many 2s, 3s, and 4s. I typed the following formula to count the number of ones.

=COUNTIF(SHEET1:SHEET9!B20,1)

When I do this I get #VALUE inside Sheet 10 instead of the count I want. If I use a straight COUNT command such as:

=COUNT(SHEET1:SHEET9!B20)

everything works fine. But of course this command just counts cells that have something in them and no criteria is used. Does the COUNTIF function simply not work across sheets in a workbook?

Any help will be greatly appreciated.

Thanks,

Joe Nowak

2. ## Re: COUNTIF across sheets of a workbook (2000)

As far as I know, this is indeed the case. The countif function does not accept ranges defined across worksheets. Below I created a UserDefined function that asks for a range-address (you should enter e.g. "B20"; thus as text, between quotes) and a criterion (also enter the criterion as text, e.g. ">4") and counts the number of occurrences fulfilling the criterion across all sheets in the workbook.

<pre>Function CountIfAcrossSheets(CountRange As String, Criterion As String) As Integer
Dim cell As Range
Dim oSheet As Worksheet
Dim R As Range
For Each oSheet In ActiveWorkbook.Worksheets
Set R = oSheet.Range(CountRange)
For Each cell In R
If Evaluate(Str\$(cell.Value) & Criterion) Then
CountIfAcrossSheets = CountIfAcrossSheets + 1
End If
Next
Next
End Function
</pre>

#### Posting Permissions

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