COUNTIF or SUMIF Problem
 

Hi folks:

I have a Bill-of-Materials where the parts used in a product are referenced
like C11, R22, etc.

The contents of C2 a C13,C16,C18,C19,C23

To get a quantity of the parts used of each type, is it possible to count
the occurences of "C" (or R, or anything else) ?

I've tried =COUNTIFSheet1!,"*C*" and variants

Would appriciate any help - Many thanks

Steve

Have you tried

=COUNTIF(Sheet1!A:A,"*C*")

where A is the column with the data in

--

HTH

RP
(remove nothere from the email address if mailing direct)


"s boak" wrote in message
...
Hi folks:

I have a Bill-of-Materials where the parts used in a product are
referenced
like C11, R22, etc.

The contents of C2 a C13,C16,C18,C19,C23

To get a quantity of the parts used of each type, is it possible to count
the occurences of "C" (or R, or anything else) ?

I've tried =COUNTIFSheet1!,"*C*" and variants

Would appriciate any help - Many thanks

Steve

If the part numbers are listed in several cells (ie 1
part number per cell), then you've got it:

=COUNTIF(A1:A5,"*C*")

But if parts are listed in *one* cell with a comma
delimiter, then use:

=LEN(A1)-LEN(SUBSTITUTE(A1,"C",""))

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi folks:

I have a Bill-of-Materials where the parts used in a
product are referenced
like C11, R22, etc.

The contents of C2 a C13,C16,C18,C19,C23

To get a quantity of the parts used of each type, is it
possible to count
the occurences of "C" (or R, or anything else) ?

I've tried =COUNTIFSheet1!,"*C*" and variants

Would appriciate any help - Many thanks

Steve


.

Thanks so much Jason
The second one did the trick !!
Steve

"Jason Morin" wrote in message
...
If the part numbers are listed in several cells (ie 1
part number per cell), then you've got it:

=COUNTIF(A1:A5,"*C*")

But if parts are listed in *one* cell with a comma
delimiter, then use:

=LEN(A1)-LEN(SUBSTITUTE(A1,"C",""))

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi folks:

I have a Bill-of-Materials where the parts used in a
product are referenced
like C11, R22, etc.

The contents of C2 a C13,C16,C18,C19,C23

To get a quantity of the parts used of each type, is it
possible to count
the occurences of "C" (or R, or anything else) ?

I've tried =COUNTIFSheet1!,"*C*" and variants

Would appriciate any help - Many thanks

Steve


.

. . . and case sensitive as well ...
Way cool
Thanks again Jason
Steve

"Jason Morin" wrote in message
...
If the part numbers are listed in several cells (ie 1
part number per cell), then you've got it:

=COUNTIF(A1:A5,"*C*")

But if parts are listed in *one* cell with a comma
delimiter, then use:

=LEN(A1)-LEN(SUBSTITUTE(A1,"C",""))

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi folks:

I have a Bill-of-Materials where the parts used in a
product are referenced
like C11, R22, etc.

The contents of C2 a C13,C16,C18,C19,C23

To get a quantity of the parts used of each type, is it
possible to count
the occurences of "C" (or R, or anything else) ?

I've tried =COUNTIFSheet1!,"*C*" and variants

Would appriciate any help - Many thanks

Steve


.