Countif using the same cell on every sheet

  • Thread starter Thread starter kmh987
  • Start date Start date
K

kmh987

Is it possible to do a countif using the same cell reference for every
worksheet?

Like a shorthand of doing but i would also like to covert it to VB
code
=COUNTIF('AU05-02'!D16,"X")+COUNTIF('AU05-09'!D16,"X")+COUNTIF('AU05-12'!D16,"X")+COUNTIF('AU05-14'!D16,"X")+COUNTIF('AU05-23'!D16,"X")+COUNTIF('AU04-24'!D16,"X")+COUNTIF('AU05-29'!D16,"X")+COUNTIF('AU05-34'!D16,"X")+COUNTIF('AU05-36&7'!D16,"X")+COUNTIF('AU05-46'!D16,"X")+COUNTIF('AU05-50'!D16,"X")+COUNTIF('AU05-58'!D16,"X")+COUNTIF('AU05-60'!D16,"X")+COUNTIF('AU05-61'!D16,"X")+COUNTIF('AU05-68'!D16,"X")

Here is a picture that might help you to understand what i would like
to be done.
4643
This survey is on every one of the 16 worksheets but the first
worksheet (Summary 2004_5) is blank and i would like a summary of all
the "X" on all the other worksheets.
If at all possible, if a new worksheet is added or name is changed it
would automatically include that one aswell

Thanks in advance
Kieran


+-------------------------------------------------------------------+
|Filename: excelexample.jpg |
|Download: http://www.excelforum.com/attachment.php?postid=4643 |
+-------------------------------------------------------------------+
 
Function CountACrossSheets(cell As String, testValue) As Long
Dim sh As Worksheet
Dim rng As Range
Dim tmp As Long
For Each sh In ThisWorkbook.Worksheets
If sh.Name <> Range("A1").Parent.Name Then
Set rng = sh.Range(cell)
tmp = tmp + Application.CountIf(rng, testValue)
End If
Next sh
CountACrossSheets = tmp
End Function


use like this

=CountAcrossSheets("D16","X")

it will count all sheets except the sheet the function is used in.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
slight adjustment

Function CountACrossSheets(cell As String, testValue) As Long
Dim sh As Worksheet
Dim rng As Range
Dim tmp As Long

For Each sh In ThisWorkbook.Worksheets
If sh.Name <> Application.Caller.Parent.Name Then
Set rng = sh.Range(cell)
tmp = tmp + Application.CountIf(rng, testValue)
End If
Next sh
CountACrossSheets = tmp
End Function


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Help i have entered the VB code and then i go to enter the formula into
the cell but i get a #NAME? error and i can't work out why??
 
You need to put the VBA code in a standard code module, not in a worksheet
code module, nor ThisWorkbook.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Back
Top