Countif for Visible Rows Only

[MikeDBMan]

I wish to count rows only of they are visible rows. Is there an existing function for doing so?

In other words, let's say I have a table that has a column Q1,Q2, Q3, Q4 for the financial period, Quarter 1, Quarter 2, etc. I wish to count how many entries exist in the table For Q1 that are visible rows. So if a row is filtered out or hidden it would NOT be counted. Thanks for your help!

 

[T. Valko]

I wish to count rows only of they are visible rows. Is there an existing function for doing so?

In other words, let's say I have a table that has a column Q1,Q2, Q3, Q4 for the financial period, Quarter 1, Quarter 2, etc. I wish to count how many entries exist in the table For Q1 that are visible rows. So if a row is filtered out or hidden it would NOT be counted. Thanks for your help!

This formula will count only the filtered visible cells in the range A5:A20 that actually contain some entry:

=SUBTOTAL(3,A5:A20)

 

[MikeDBMan]

You reply is not exactly my problem I don't think. I only want to count how many visible cells in a column have Q4 in them. So I need to do a qualified Countif. I want to know how many visible rows have Q4 in them.

 

[T. Valko]

You reply is not exactly my problem I don't think. I only want to count how many visible cells in a column have Q4 in them. So I need to do a qualified Countif. I want to know how many visible rows have Q4 in them.

Ok, this will perform a "count if" on a filtered range.

Count only the visible cells that contain Q4.

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A20,ROW(A5:A20)-ROW(A5),0,1)),--(A5:A20="Q4"))

 

[MikeDBMan]

That is the perfect answer for my problem. Thanks so much!

 

[T. Valko]

That is the perfect answer for my problem. Thanks so much!

You're welcome. Thanks for the feedback!