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.
excelexample.jpg
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

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

"kmh987" <kmh987.26dxwy_1145238301.8343@excelforum-nospam.com> wrote in
message news:kmh987.26dxwy_1145238301.8343@excelforum-nospam.com...
>
> 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
>

[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" <kmh987.26dxwy_1145238301.8343@excelforum-nospam.com> wrote in
message news:kmh987.26dxwy_1145238301.8343@excelforum-nospam.com...
>
> 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
>

[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?? I have entered the function into the sheet, and also tried it in ThisWorkbook. Any ideas

[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" <kmh987.26equn_1145275805.6091@excelforum-nospam.com> wrote in
message news:kmh987.26equn_1145275805.6091@excelforum-nospam.com...
>
> 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??
>
>
> --
> kmh987
> ------------------------------------------------------------------------
> kmh987's Profile:
http://www.excelforum.com/member.php...o&userid=33533
> View this thread: http://www.excelforum.com/showthread...hreadid=533335
>

[kmh987]

you are an absolute legend
thanks alot