Count cells after applying an Excel filter Solved

Question

Hello,

I have a small question: I have a table in Excel and I'm supposed to count the occurrences of the word "Médecin" in a column (to get the total). The problem is that when I apply a filter and use "countif()", the function counts everything, including the cells that are hidden by the filter, while I only need the filtered data. Thank you in advance for your answer.

Gabriel

Configuration: Windows XP Internet Explorer 6.0

Mabelle60 · Accepted Answer

Hi
The SUBTOTAL function addresses this issue

=SUBTOTAL(3,'your field') : counts the number of displayed values
=SUBTOTAL(9,'your field') : sums the values as long as they are numeric

There are others (from 1 to 9 I think) but I only remember these two. I'll find them again..

Mabelle60 · Answer

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+

eriiic · Answer

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

Raymond PENTIER · Answer

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

eriiic · Answer

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

regan80 · Answer

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

babach · Answer

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

djib1101 · Answer

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.

Fethi93 · Answer

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

number6six · Answer

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)

Count cells after applying an Excel filter

10 réponses

Big fish games are they worth it?

Saracroche: the effective french app against phone spam

How to get the character µ on the android keyboard.

Texture issues in enshrouded

Access issue to my "contact me" profile

Windows spotlight is not working.

Black screen digital camera

Connecting rj45 cable with fiber

Sony bravia tv red led blinks 4 times

Free: associating with the freebox server in progress