Counting occurrences of products in a master list

[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?

 

[Sheeloo]

Try
(I tested in Excel 2007)

=SUMPRODUCT(--(LEFT(A1:A2000,Len(F1))=F1))

 

[T. Valko]

Another possibility:

F1 = RN7A

=COUNTIF(A1:A2000,F1&"*")

 

[robert]

Another possibility:

F1 = RN7A

=COUNTIF(A1:A2000,F1&"*")

I had tried this but it picks up any combinations, for example it will count:

RN7A
RN7A/9
RN7A/G4

but also

RN7AA
RN7AB
RN7AX... etc.

I see there's another suggestion which I haven't tried yet.

Thank you for your answer!

 

[T. Valko]

I had tried this but it picks up any combinations, for example it will
count:

RN7A
RN7A/9
RN7A/G4

but also

RN7AA
RN7AB
RN7AX... etc.

I see there's another suggestion which I haven't tried yet.

Thank you for your answer!

So, you only want to count cells that contain RN7A or RN7A followed by a / ?

Try this:

F1 = RN7A

=COUNTIF(A1:A2000,F1)+COUNTIF(A1:A2000,F1&"/*")

I see there's another suggestion which I haven't tried yet.

That'll do the same thing as my original suggestion.

 

[robert]

I see I should be top posting... sorry...

This one is closer but not 100%.

When searching for RN7A in the list, it ignores RN7 (correctly) and counts
RN7A, and RN7A/5 as expected. But it also counts RN7AA and RN7AB, which it
should not.

I can verbally say what the formula should do, but I can't put it into a code!

"Look for F1 and count the occurrences in the range A1:A2000. And also, look
for F1 with a trailing / character and add those occurrences to determine the
total"

The formula needs to do two simultaneous counts and add them together.

 

[robert]

YES! That seems to do it, and it follows the "saying the formula" example I
gave. It always looks so simple, after someone figures it out for you!

Thank you very much!

 

[T. Valko]

You're welcome. Thanks for the feedback!