Count cells after applying an Excel filter

Re
Oh no, there are 11:
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VAR.P

A+

One solution is to create a column that tells you whether the row is hidden or not:

A column with the formula =SUBTOTAL(3;D3), with D3 being a cell that is always filled (for example, the row number/counter)

And then use the COUNTIFS function, filter on this column and on the column with your criteria to count.

Thus, when you apply the filters, the SUBTOTALs will be hidden and display '0', and only count your criteria on the visible rows.

Hello,

you can create a custom function (in a module, not in the sheet code) that returns 1 if the row of the cell is visible or 0 if it is hidden.

Function visible(cell As Range) As Integer visible = (Not cell.EntireRow.Hidden) * -1 End Function

- Remove all filters.
- Add in C2: =visible(C2) and copy it down (this column can be hidden if needed)

- With the "doctor" in column B
=SUMPRODUCT((B2:B10="doctor")*C2:C10) returns the number of visible "doctors".

Apply the filters and test

eric

I had the same problem and the proposal with =SUBTOTAL works like a charm

you need to choose function_num=3, which corresponds to COUNTA

so if my entire column is selected, I get 618 items
if I only select 'doctor', I get 213 items
and so on

=SUBTOTAL(3;A2:A619)

Hi, Igabriel.
And simply, without using a macro, if you applied the SUMPRODUCT function mentioned by eriiic to the "Date" and "Appellation" columns? It's quick and reliable, and it perfectly meets your request.
--
Retirement is great! Especially in the Caribbean... :-)
Raymond

Hello,

If you apply your filter by macro directly in the macro without using a function, then... no?

Hello Gabriel,
I would like to know if you have found a solution to your problem because I have the same issue?
Thank you

create a column with the formula if =IF(A1="doctor",1,0)
(A1 being the cell in the column that may eventually contain the word doctor)
then create a subtotal above the label of the column containing this formula
the subtotal will thus be equal to the number of cells containing the word doctor

Hello,

For your problem, you need to use VBA /

SUB MacroNbMedecin ()

dim nbligne as long
dim colonne as long
dim nbMedecin as long
dim nbcolonne as long
dim rg as range

colonne = Put the number of the column you want to filter

'Search for the first non-empty row
lignedebut= cells(1,colonne).end(xldown).row

'Count the number of rows and columns in the range
nbligne= cells(rows.count,colonne).end(xlup).row
nbcolonne = cells(lignedebut,columns.count).end(xltoleft).column

'Get the range
Set rg = Cells(lignedebut, 1).Resize(nbligne, nbcolonne)

'We filter
rg(1, 1).Offset(-1, 0).AutoFilter Field:=colonne, Criteria1:="=MEDECIN"

'Check if it exists
On Error Resume Next
If IsError(rg.SpecialCells(xlCellTypeVisible)) Then
On Error GoTo finProcRecupGP3Bis:
MsgBox "No data contains the word MEDECIN", vbInformation, "Data"
End '==> exit the function
End If

'To count, you do
nbMedecin = rg.SpecialCells(xlCellTypeVisible).Count / nbcolonne

cells(1,1) = nbMedecin '==> place the number of doctors in cell A1

end SUB