R
robert
I want to count the number of occurrences of a small list of products that
appear in a large (2,000+) inventory list. I used the countif function but
there's a specific situation where my formula fails. Here's the example of
the product numbers in the small list:
R6AA
RM8B
RN7A
RN7AA
RN7A/5
RN7AB
The product numbers in the master inventory list may be 3 to 12 characters
in length. Some of them include the / separator which designates a subset of
the master number to the left of the /.
For example, the fifth item (RN7A/5) is the same as RN7A. I would like any
products with the / character to be totaled with the "root" item (the number
to the left of the /).
The product numbers are in random order in the inventory list, column A. The
couple of dozen items to be searched and counted are in column F.
I started in column H1 with =countif(A1:A2000,f1) and copied down. As long
as I include RN7A and RN7A/5 in column F, this works fine. I get my totals
but the subsets are not combined.
I would like to only specify RN7A and it's total would include all subset
products /5, /7, /G4, etc. too.
Can anyone provide a formula to accomplish this goal?
appear in a large (2,000+) inventory list. I used the countif function but
there's a specific situation where my formula fails. Here's the example of
the product numbers in the small list:
R6AA
RM8B
RN7A
RN7AA
RN7A/5
RN7AB
The product numbers in the master inventory list may be 3 to 12 characters
in length. Some of them include the / separator which designates a subset of
the master number to the left of the /.
For example, the fifth item (RN7A/5) is the same as RN7A. I would like any
products with the / character to be totaled with the "root" item (the number
to the left of the /).
The product numbers are in random order in the inventory list, column A. The
couple of dozen items to be searched and counted are in column F.
I started in column H1 with =countif(A1:A2000,f1) and copied down. As long
as I include RN7A and RN7A/5 in column F, this works fine. I get my totals
but the subsets are not combined.
I would like to only specify RN7A and it's total would include all subset
products /5, /7, /G4, etc. too.
Can anyone provide a formula to accomplish this goal?