# Microsoft Office Application Help - Excel Help forum > Excel General >  >  Applying a COUNTIF formula only to visible cells in a filtered list.

## sandra hills

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

----------


## JBeaucaire

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.

----------


## ncmay

Look at the SUBTOTAL function

----------


## DonkeyOte

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.

----------


## 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)

----------


## sandra hills

> 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

----------


## daddylonglegs

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

----------


## desadarius

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?

----------


## martindwilson

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

----------

