Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
. . . 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Forget SUMIF, COUNTIF and VLOOKUP | Excel Worksheet Functions | |||
using sumif & countif to sort multiple cells | Excel Worksheet Functions | |||
SUMIF problem | Excel Worksheet Functions | |||
Modify SUMIF and COUNTIF to work with SUBTOTALS | Excel Worksheet Functions | |||
Countif, Sumif, If - help! | Excel Worksheet Functions |