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" wrote in message
...
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.