COUNTIF___Visable only
 

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

COUNTIF___Visable only
 

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

--
__________________________________
HTH

Bob

"Skinman" wrote in message
...
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

COUNTIF___Visable only
 

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

"Bob Phillips" wrote in message
...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$20)-ROW($D$1),,1)),--($D$2:$D$20="Assigned"))

--
__________________________________
HTH

Bob

"Skinman" wrote in message
...
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

COUNTIF___Visable only
 

What is the filtered column?

--
__________________________________
HTH

Bob

"Skinman" wrote in message
...
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

"Bob Phillips" wrote in message
...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$20)-ROW($D$1),,1)),--($D$2:$D$20="Assigned"))

--
__________________________________
HTH

Bob

"Skinman" wrote in message
...
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

COUNTIF___Visable only
 

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

"Bob Phillips" wrote in message
...
What is the filtered column?

--
__________________________________
HTH

Bob

"Skinman" wrote in message
...
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

"Bob Phillips" wrote in message
...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$20)-ROW($D$1),,1)),--($D$2:$D$20="Assigned"))

--
__________________________________
HTH

Bob

"Skinman" wrote in message
...
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