Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Thanked 0 Times in 0 Posts

    COUNTIF across sheets of a workbook (2000)


    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.


    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:


    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.


    Joe Nowak

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Kortrijk, Belgium
    Thanked 0 Times in 0 Posts

    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
    End Function

Posting Permissions

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