| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
