Countif using the same cell on every sheet

[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 |
+-------------------------------------------------------------------+

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[kmh987]

great thanks for your help i really appreciate it
Kieran

 

[kmh987]

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??

 

[Bob Phillips]

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)

 

[kmh987]

you are an absolute legend
thanks alo