Count if across tabs

[MartinL]

I have a workbook with 52 tabs all identical in layout
Example
I am trying to create a formula that will look at cell D2 in each tab (except the one i am in) and count if the cell = 'x'
is it is easy to do this or do I need to create a megaformula

 

[Eric W]

If the choice is between an "x" or nothing, then you can use a fairly simple 3-D formula, like in B3 shown here. Make sure that there are no sheets between the start and end sheet that you don't want included.

If there could be something else in D2, then you could use a formula like in C5 here. Put the list of sheets you want to include in B5:B6 (or whatever).


Excel 2012

	B	C	D
x
Sheet6
Sheet7

<tbody>
[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B3[/TH]
[TD="align: left"]=COUNTA(Sheet6:Sheet7!D2)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C5[/TH]
[TD="align: left"]=SUMPRODUCT(COUNTIF(INDIRECT("'"&B5:B6&"'!D2"),"x"))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bobsan42]

try this function:

Function CountIfSheets(SearchRange As Range, LookFor As Variant, Optional ExcludeThisSheet As Boolean = True)
    Dim wsh As Worksheet
    Dim rng As String: rng = SearchRange.Address
    Dim wnm As String: wnm = Application.Caller.Parent.Name
    
    CountIfSheets = 0
    
    For Each wsh In ThisWorkbook.Worksheets
        If ExcludeThisSheet Then
            If wsh.Name <> wnm Then
                If wsh.Range(rng).Value = LookFor Then CountIfSheets = CountIfSheets + 1
            End If
        Else
            If wsh.Range(rng).Value = LookFor Then CountIfSheets = CountIfSheets + 1
        End If
    Next wsh
    
    Set wsh = Nothing
    
End Function

 

[MartinL]

If the choice is between an "x" or nothing, then you can use a fairly simple 3-D formula, like in B3 shown here. Make sure that there are no sheets between the start and end sheet that you don't want included.

Hi Eric W

Your solution =COUNTA(Sheet2:Sheet62!D2) looked as if it was exactly what I needed however Excel refused to accept it as a formula my worksheet was Sheet1 so there were no sheets in between. However the sheets had been renamed, so by looking at the VBA screen I found that the sheets were in order 2 to 62

But thanks for your help.

Martin

 

[MartinL]

Hi Bobsan42

I was hoping to avoid VBA - people here don't like it much, but the function was actual good and worked first time so we have gotten away with it.

Thank you very much.

Martin

 

[Eric W]

It may be that Excel didn't take the formula if you renamed the Sheets with spaces in them. In such a case, you'd need to put quotes around the sheet names like so:

=COUNTA('Jan 2019:Mar 2019'!D2)

Some people like to create a couple of "dummy" sheets named Start and Finish. Then put those around the other sheets you want to check and use

=COUNTA(Start:Finish!D2)

If you ever add sheets to your range, just drag the Finish sheet tab to include the new sheets.

 

[MartinL]

Hi Eric W

Yep that works...Thanks very much

 

[Eric W]

You're welcome.