# COUNTIF___Visable only

S

#### Skinman

Hi
Using Excel 2007
Would like to count cells that meet certain criteria
Would like it to work in a filtered spreadsheet to countif
only on visible cells
Need something like SUBTOTALIF
I can not work out how to do this without filtering down several times
using Subtotal function but then I lose the bigger picture
Any help appreciated

B

#### Bob Phillips

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW(\$C\$2:\$C\$20)-ROW(\$D\$1),,1)),--(\$D\$2:\$D\$20="Assigned"))

S

#### Skinman

I'm not quite there yet Bob little more help if you please
So far:-
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C5,ROW(\$C\$6:\$C\$6000)-ROW(\$H\$4),,1)),--(\$E\$6:\$E\$6000="50"))
Data range A6 to AP6000 with headers in row 5
=SUBTOTAL(3,\$E6:\$E\$6000) is in cell H4
The range I am searching is E6:E6000__ Searching for 50
Getting 0 I know the answer is 819

B

#### Bob Phillips

What is the filtered column?

S

#### Skinman

I wanted it to work for any column that was filtered
Like this formula does for showing me the most same numbers in column E
that this newsgroup kindly helped me with.
{=MODE(IF(SUBTOTAL(3,OFFSET(Disperse,ROW(Disperse)-MIN(ROW(Disperse)),0,1)),Disperse))}
"Disperse" is the named range in column E as in next line
=OFFSET(Action!\$E\$6,0,0,COUNT(OFFSET(Action!\$C\$6,0,0,9999)),1)
Column C is only column guaranteed to have data in every cell
Thanks for your trouble but still working on solution