Auto filter formula - help needed

[Greeny12229]

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

[Krishnakumar]

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

[Greeny12229]

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
>
>