count specific value with filtered data

I am trying to count the number of times a part number appears in a column of
filtered data. The part number is repeated because I need to track serial
numbers with it. I tried using the Countif function but it takes into
accound values that are filtered out. I sorted my data by part number and
used the subtotal function with CountA. This gives me an accurate total but
I have to constantly change the range when I add more parts to the
spreadsheet.

Should I be using the Subtotal function or is there another way to total my
values without having to constantly make modifications? Thank you for any
help provided.

=SUMPRODUCT(--($B$2:$B$200=Part#),SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$200)-MIN(RO
W($B$2:$B$200)),,)))

will count Part# in a filtered list B2:B200, adapt to fit



--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Shawn13" <Shawn13@discussions.microsoft.com> wrote in message
news:D1AEF898-2A8F-4B86-A7F8-8E960500DAB5@microsoft.com...
>I am trying to count the number of times a part number appears in a column
>of
> filtered data. The part number is repeated because I need to track serial
> numbers with it. I tried using the Countif function but it takes into
> accound values that are filtered out. I sorted my data by part number and
> used the subtotal function with CountA. This gives me an accurate total
> but
> I have to constantly change the range when I add more parts to the
> spreadsheet.
>
> Should I be using the Subtotal function or is there another way to total
> my
> values without having to constantly make modifications? Thank you for any
> help provided.