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 au
code"),--($c$9:$c$9994))

HT

 

[Greeny12229]

HI Krish,

That works great, thanks very much for your assistance - it's appreciated.

Matt