# Applying a COUNTIF formula only to visible cells in a filtered list.

1. ## Applying a COUNTIF formula only to visible cells in a filtered list.

The title pretty much sums the query up. I have a table of data with formulas (eg COUNTIF ) applied to each column. When I filter the column, by date range or region, say, I would like the formula to return the result just for the visible (filtered) entries. This is the same concept as the SUBTOTAL formula, which provides this functionality but only on a limited number of functions (SUM, AVG,MAX etc).
Any help solving this would be much appreciated.
Thanks
Sandra  Register To Reply

2. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list.

SUBTOTAL() has a great many functions: ``Please Login or Register  to view this content.``
I believe the 100-level codes are not available in Excel 2000, but all the others are, including count.  Register To Reply

3. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list.

Look at the SUBTOTAL function  Register To Reply

4. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list.

Given you already make mention of the SUBTOTAL function I presume you know what you can and can't do with it - if you want to do conditional calculations on visible rows (ie COUNTIF) then you're looking at using SUBTOTAL embedded within SUMPRODUCT (or using helpers) - we'd need more info to provide more assistance - ie what are you doing exactly ? Post a sample file if you feel it will help illustrate the issue(s) at hand.  Register To Reply

5. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list.

If you want to count the "x"s in A2:A100....but only on rows visible after filtering then try this formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)  Register To Reply

6. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list. Originally Posted by daddylonglegs If you want to count the "x"s in A2:A100....but only on rows visible after filtering then try this formula

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),(A2:A100="x")+0)
Hi Daddylonglegs
I'm new to using the forum and am not sure of the protocol/procedures for posts and replies etc. but I have tried the SUMPRODUCT formula you suggested and it has worked which is Brilliant! I am keen to understand the structure of the formula and what it is essentially doing - it seems to be based around the SUBTOTAL function but I am not sure about the OFFSET part and the A2 ROW..... part and why you need the SUMPRODUCT bit too? If you are able to give me any insights, this will really help with future projects. Many Thanks for your asssistance. Sandra  Register To Reply

7. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list.

This part

SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0))

returns a 1 for each row in the range if it's visible after filtering, otherwise a zero.

SUMPRODUCT is a standard way to count with multiple criteria, in this case your 2 criteria are whether the cell is visible....and whether it contains "x". See an exhaustive explanation of SUMPRODUCT here, one of the examples (example 11) shows a version of this type of formula....

The SUBTOTAL/OFFSET part is now a relatively well known "idiom", credited to Laurent Longre, I believe. See here for more  Register To Reply

8. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list.

Hii Daddylonglegs,

Thanks for your post...it was really useful. However, refering back to your equation SUMPRODUCT(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-ROW(A2),0)),A2:A100="X")+0). This equation works okay as long as the row A2 shows up in the visible filtered range. When a new criteria is selected and A2 is no longer visible, this equation returns an error message. Is there a way of making this equation dynamic?  Register To Reply

9. ## Re: Applying a COUNTIF formula only to visible cells in a filtered list.

this is an old post but...it should work ok but you need 103 not 3 things changed in excel 2003> see post #2  Register To Reply

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1