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.

count specific value with filtered data
 

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