Countif across multiple sheets and only on visible cells

[LaurensTa]

Hi all,

I want to create a function which counts all numbers in a column in all sheets.
I already found a function which returns all sheets names, so I have a tab which shows what sheets I have which can be found below:

[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64, align: center"]Tab names: [/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]18 Apr[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]10 Apr[/TD]
[/TR]
[TR]
[TD="class: xl64, align: center"]etc.... [/TD]
[/TR]
</tbody>[/TABLE]

Then I got in my main sheet:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Letter: [/TD]
[TD="class: xl67, width: 64"]Appearances:[/TD]
[/TR]
[TR]
[TD="class: xl67"]A[/TD]
[TD="class: xl67, align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl67"]B[/TD]
[TD="class: xl67, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl67"]C[/TD]
[TD="class: xl67, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67"]D[/TD]
[TD="class: xl67, align: right"]57[/TD]
[/TR]
[TR]
[TD="class: xl67"]E[/TD]
[TD="class: xl67, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

And I counted the appearances with the formula:
=SUMPRODUCT(COUNTIF(INDIRECT("'"&Tabs!$A$2:$A$41&"'!A:A"), A3))

But the thing is that in my sheets, I am hiding a lot of rows because they have to be excluded.
The formula that I use counts even the values which are filtered out and I would like to only count the visible cells.
I found some formulas about how to only count the visible cells, but I don't know how to combine them.

So what I want to see is a table which is for example like:
[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>[TR]
[TD="class: xl66, width: 64"]Letter: [/TD]
[TD="class: xl67, width: 64"]Appearances:[/TD]
[/TR]
[TR]
[TD="class: xl67"]A[/TD]
[TD="class: xl67, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl67"]B[/TD]
[TD="class: xl67, align: right"]8[/TD]
[/TR]
[TR]
[TD="class: xl67"]C[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl67"]D[/TD]
[TD="class: xl67, align: right"]50[/TD]
[/TR]
[TR]
[TD="class: xl67"]E[/TD]
[TD="class: xl67, align: right"]0

[/TD]
[/TR]
</tbody>[/TABLE]

which is almost the same, but you can see that it contains lower appearances since only the visible cells are counted.

Hope you can help me,
Laurens

 

[XOR LX]

Hi,

Why hide them? If you mark the rows to be excluded, e.g. with an "x" in a further column, it's a simple additional clause to your current formula.

Regards

 

[LaurensTa]

Hi,

The thing is that the person who I want to make this for, just gets the tabs like that.
He doesn't know how to put in a "X" and I don't think I can teach him (he is old..)

Currently he even manually counts how often it appears in the tabs.
I would like to help him as much as possible by creating 1 formula which he doesn't have to do anything with.

Regards,
Laurens

 

[XOR LX]

Ok, thanks.

Array formula**:

=SUM(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(Tabs!$A$2:$A$41)&"'!A1"),ROW(A1:A10)-MIN(ROW(A1:A10)),0))=A3,IF(SUBTOTAL(3,OFFSET(INDIRECT("'"&TRANSPOSE(Tabs!$A$2:$A$41)&"'!A1"),ROW(A1:A10)-MIN(ROW(A1:A10)),0)),1)))

Unfortunately you can't 'get away' with referencing an entire column (A:A) within such a construction, as you can with COUNTIF(S). Hence my choice of A1:A10, which obviously you can change, though be sure to keep as small as possible, else calculation time will be affected noticeably.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).