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?

Try
(I tested in Excel 2007)

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

"robert" wrote:

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?

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?

"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!

"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

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.



"Sheeloo" wrote:

Try
(I tested in Excel 2007)

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

"robert" wrote:

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?

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

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