Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto filter formula - help needed
Hi Guys, hope someone can help with this its v awkward:
I Have a sheet which is on auto filter- example below filtered on name A b C Name Aux Duration 1 auxwk2 5 1 auxwk2 5 1 auxwk3 10 I use the following to count occourence's of the filtered aux code's. the formula is in 10 different cells identifying 10 different aux codes =SUMPRODUCT(SUBTOTAL(3,OFFSET($b$9:$b$9994,ROW($b$ 9:$b$9994)-MIN(ROW($b$9:$b$9994)),,1)), --($b$9:$b$9994="relevant aux code")) however i need to be able to also calculate the duration of the specific aux code within the visible cells. Eg: using table above the result i would be after is (code used refrencing 2 different aux codes in 2 cells): Cell 1(aux 2) - 10 cell 2(aux3) - 10 I know i have probably complicated the above more than necersary. Can anybody help at all? Kind regards Greeny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto filter formula - help needed
Hi Greeny, Try, =SUMPRODUCT(SUBTOTAL(3,OFFSET($b$9:$b$9994,ROW($b 9:$b$9994)-MIN(ROW($b$9:$b$9994)),,1)), --($b$9:$b$9994="relevant au code"),--($c$9:$c$9994)) HT -- Krishnakuma ----------------------------------------------------------------------- Krishnakumar's Profile: http://www.excelforum.com/member.php...fo&userid=2013 View this thread: http://www.excelforum.com/showthread.php?threadid=38757 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto filter formula - help needed
HI Krish,
That works great, thanks very much for your assistance - it's appreciated. Matt "Krishnakumar" wrote: Hi Greeny, Try, =SUMPRODUCT(SUBTOTAL(3,OFFSET($b$9:$b$9994,ROW($b$ 9:$b$9994)-MIN(ROW($b$9:$b$9994)),,1)), --($b$9:$b$9994="relevant aux code"),--($c$9:$c$9994)) HTH -- Krishnakumar ------------------------------------------------------------------------ Krishnakumar's Profile: http://www.excelforum.com/member.php...o&userid=20138 View this thread: http://www.excelforum.com/showthread...hreadid=387578 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: Auto Filter Sub Total formula | Excel Discussion (Misc queries) | |||
DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
Formula for Auto filter | Excel Discussion (Misc queries) | |||
Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
fORMULA IN aUTO FILTER | Excel Programming |