Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Countif across sheets (Excel 97-sr2)

    How about:

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

    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Sep 2001
    Location
    Birmingham, Alabama, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    New Lounger
    Join Date
    Jan 2001
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •