Search code examples
excelfiltercountsubtotal

Counting Number of Columns in Filtered Excel Dataset?

I don't know if this is a very quick fix or if this is the appropriate Stack Exchange site to put this under as I am new, so I apologize in advance if I accidentally violate any clause in the meta site.

 

If Excel is appropriate to submit and you would like to help me (which would be very appreciated), my problem is as follows:

 

I have a filtered dataset whose row display is as follows: 

 1 ....DATA....
 3 ....DATA....
 4 ....DATA....
 5 ....DATA....
 6 ....DATA....
 17 ....DATA...
 23 ....DATA...
 41 ....DATA...
 81 ....DATA...

With the numbers representing the row number on the far left hand side in an Excel spreadsheet. Obviously the numbers represent the rows that satisfy the filtering condition. I want to count the number of rows that satisfy this condition for some form of data analysis. However, whenever I use the 

  count()

or 

  countA()

Function in Excel, I get 81. Logically, I understand this comes from the fact that I counted over filtered datasets to my cumulative final row, row 81. However, is there anyway to count the filtered datasets only displayed on my screen? So in this case I would like a counting function to give me 9 when use the function along the filtered dataset above.

Solution

  • You want to use the SUBTOTAL() function with the first Criterion set to 3 which is CountA():

    =SUBTOTAL(3,A2:A29)

    enter image description here