You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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

"robert" wrote in message
...


"T. Valko" wrote:

Another possibility:

F1 = RN7A

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

--
Biff
Microsoft Excel MVP


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



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.


--
Biff
Microsoft Excel MVP