We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these part
numbers begin with either "2D7" or "CCA". I have done a similar calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is the
column that has the invoice numbers.

The problem I am having is that the column with the part numbers has all
part number but I only want the special numbers to be calculated.

use two helper columns
in the first
=right(part_number,len(part_number)-3)
in the second use your countif on the first helper.

"Amber" wrote:

We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these part
numbers begin with either "2D7" or "CCA". I have done a similar calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is the
column that has the invoice numbers.

The problem I am having is that the column with the part numbers has all
part number but I only want the special numbers to be calculated.

Maybe something like

=SUMPRODUCT(--(LEFT(A5:A100,3)="2D7"))+SUMPRODUCT(--(LEFT(A5:A100,3)="CCA"))

Hope this helps,

Hutch

"Amber" wrote:

We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these part
numbers begin with either "2D7" or "CCA". I have done a similar calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is the
column that has the invoice numbers.

The problem I am having is that the column with the part numbers has all
part number but I only want the special numbers to be calculated.

See your original post!

It's usually best to stay in the original thread so that everyone can see
previous suggestions and eliminate duplication of effort.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Amber" wrote in message
...
We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these part
numbers begin with either "2D7" or "CCA". I have done a similar
calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is
the
column that has the invoice numbers.

The problem I am having is that the column with the part numbers has all
part number but I only want the special numbers to be calculated.

BJ,
Thanks for the info but I need this in more lamine terms. I am not
understanding what exactly you are trying to say. The right function will
extract the characters from the right. So I am unsure of how that relates to
what I am trying to do. Could you please clarify?

"bj" wrote:

use two helper columns
in the first
=right(part_number,len(part_number)-3)
in the second use your countif on the first helper.

"Amber" wrote:

We run catalogs with special part numbers. I am trying to see how many
unique special part numbers I have as each day passes. Each of these part
numbers begin with either "2D7" or "CCA". I have done a similar calculation
for unique invoice numbers, =IF(COUNTIF($D$2:D2,D2)=1,D2,"") where D is the
column that has the invoice numbers.

The problem I am having is that the column with the part numbers has all
part number but I only want the special numbers to be calculated.