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
 
Ad

Advertisements

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
 
Ad

Advertisements

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top