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)

"kmh987" wrote in
message ...

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('AU
05-36&7'!D16,"X")+COUNTIF('AU05-46'!D16,"X")+COUNTIF('AU05-50'!D16,"X")+COUN
TIF('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 |
+-------------------------------------------------------------------+

--
kmh987
------------------------------------------------------------------------
kmh987's Profile:
http://www.excelforum.com/member.php...o&userid=33533
View this thread: http://www.excelforum.com/showthread...hreadid=533335