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:
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
Then I got in my main sheet:
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
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:
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>
</tbody>
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
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:
Tab names: |
18 Apr |
10 Apr |
etc.... |
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>
Then I got in my main sheet:
Letter: | Appearances: |
A | 25 |
B | 10 |
C | 8 |
D | 57 |
E | 1 |
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>
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:
Letter: | Appearances: |
A | 5 |
B | 8 |
C | 3 |
D | 50 |
E | 0 |
<colgroup><col width="64" span="2" style="width: 48pt;"></colgroup><tbody>
</tbody>
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