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

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

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