Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
I have a table of customers by row with the orders in one column. Thus a
single cell may contain ordered items represented as "A", "B", "C", etc. or "1", "2", "3", etc. So Jill could have an order "B,B" which would represent and order for "two item B's" Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1 item "1" and 2 item "2's". How can these "items" be tabulated for the total worksheet order to list a total for all the item "A's", "B's", etc and the items "1's", "2's", etc. Thanks for any help - for the future we need to see if the worksheet could be designed to comply with the lab's request to have the order listed in a single cell and still be able to compile a tabulation for invoicing purposes. |
#2
![]() |
|||
|
|||
![]()
Hi!
Assume the order numbers are in col A: =SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A","")))) In the SUBSTITUTE function, replace "A" with the specific character that you want to count. Alpha charaters need to be enclosed in quotes, numbers do not. This is case sensitive! Better yet, use a cell to hold the character that you want to count and use that cell reference in the SUBSTITUTE function. Biff -----Original Message----- I have a table of customers by row with the orders in one column. Thus a single cell may contain ordered items represented as "A", "B", "C", etc. or "1", "2", "3", etc. So Jill could have an order "B,B" which would represent and order for "two item B's" Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1 item "1" and 2 item "2's". How can these "items" be tabulated for the total worksheet order to list a total for all the item "A's", "B's", etc and the items "1's", "2's", etc. Thanks for any help - for the future we need to see if the worksheet could be designed to comply with the lab's request to have the order listed in a single cell and still be able to compile a tabulation for invoicing purposes. . |
#3
![]() |
|||
|
|||
![]()
Biff:
THANK YOU!! I and the high school student working with me partime don't fully understand the formula, but it WORKS!! If you can offer any explanation about some parts of the formula - we don't understand inside the first "(" what are the "--" for? AND the minus LEN statement what is the empty "quotes", "" for? followed by the "))))" We are going to be searching out what a "LEN" statement is all about - perhaps that will help us understand. But in the mean time THANK YOU - over two hours of questionable hand tabulation is being reduced to a a half hour of formula entry and worksheet arrangement for the final tabulation. THANKS! Wayne "Biff" wrote: Hi! Assume the order numbers are in col A: =SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A","")))) In the SUBSTITUTE function, replace "A" with the specific character that you want to count. Alpha charaters need to be enclosed in quotes, numbers do not. This is case sensitive! Better yet, use a cell to hold the character that you want to count and use that cell reference in the SUBSTITUTE function. Biff -----Original Message----- I have a table of customers by row with the orders in one column. Thus a single cell may contain ordered items represented as "A", "B", "C", etc. or "1", "2", "3", etc. So Jill could have an order "B,B" which would represent and order for "two item B's" Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1 item "1" and 2 item "2's". How can these "items" be tabulated for the total worksheet order to list a total for all the item "A's", "B's", etc and the items "1's", "2's", etc. Thanks for any help - for the future we need to see if the worksheet could be designed to comply with the lab's request to have the order listed in a single cell and still be able to compile a tabulation for invoicing purposes. . |
#4
![]() |
|||
|
|||
![]()
Hi
see: http://www.mcgimpsey.com/excel/formulae/doubleneg.html and http://www.xldynamic.com/source/xld.SUMPRODUCT.html for the double minus The SUBSTITUTE formula part replaces all 'A' characters with 'nothing' (""). So in total this formula evaluates the number of 'A' in a string by subtracting the length of the string without 'A's from the original string length -- Regards Frank Kabel Frankfurt, Germany "WIM4246" schrieb im Newsbeitrag ... Biff: THANK YOU!! I and the high school student working with me partime don't fully understand the formula, but it WORKS!! If you can offer any explanation about some parts of the formula - we don't understand inside the first "(" what are the "--" for? AND the minus LEN statement what is the empty "quotes", "" for? followed by the "))))" We are going to be searching out what a "LEN" statement is all about - perhaps that will help us understand. But in the mean time THANK YOU - over two hours of questionable hand tabulation is being reduced to a a half hour of formula entry and worksheet arrangement for the final tabulation. THANKS! Wayne "Biff" wrote: Hi! Assume the order numbers are in col A: =SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A","")))) In the SUBSTITUTE function, replace "A" with the specific character that you want to count. Alpha charaters need to be enclosed in quotes, numbers do not. This is case sensitive! Better yet, use a cell to hold the character that you want to count and use that cell reference in the SUBSTITUTE function. Biff -----Original Message----- I have a table of customers by row with the orders in one column. Thus a single cell may contain ordered items represented as "A", "B", "C", etc. or "1", "2", "3", etc. So Jill could have an order "B,B" which would represent and order for "two item B's" Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1 item "1" and 2 item "2's". How can these "items" be tabulated for the total worksheet order to list a total for all the item "A's", "B's", etc and the items "1's", "2's", etc. Thanks for any help - for the future we need to see if the worksheet could be designed to comply with the lab's request to have the order listed in a single cell and still be able to compile a tabulation for invoicing purposes. . |
#5
![]() |
|||
|
|||
![]()
FranK - Hi,
Thanks for the explanation - it will take some further study for me to get completely familar with it. Being pointed in the right direction with some explanation is a BIG HELP - THANKS! I'm a professional photographer and just recently photographed a family from Germany. The wife's parents celebrated 50 yrs of marriage so all the children with their families came home. My wife from Sweden also has relatives in Germany - We look forward to visiting - hopefully soon. Thanks, Wayne Makeeff Des Moines, IA - USA "Frank Kabel" wrote: Hi see: http://www.mcgimpsey.com/excel/formulae/doubleneg.html and http://www.xldynamic.com/source/xld.SUMPRODUCT.html for the double minus The SUBSTITUTE formula part replaces all 'A' characters with 'nothing' (""). So in total this formula evaluates the number of 'A' in a string by subtracting the length of the string without 'A's from the original string length -- Regards Frank Kabel Frankfurt, Germany "WIM4246" schrieb im Newsbeitrag ... Biff: THANK YOU!! I and the high school student working with me partime don't fully understand the formula, but it WORKS!! If you can offer any explanation about some parts of the formula - we don't understand inside the first "(" what are the "--" for? AND the minus LEN statement what is the empty "quotes", "" for? followed by the "))))" We are going to be searching out what a "LEN" statement is all about - perhaps that will help us understand. But in the mean time THANK YOU - over two hours of questionable hand tabulation is being reduced to a a half hour of formula entry and worksheet arrangement for the final tabulation. THANKS! Wayne "Biff" wrote: Hi! Assume the order numbers are in col A: =SUMPRODUCT(--(LEN(A1:A50)-LEN(SUBSTITUTE(A1:A50,"A","")))) In the SUBSTITUTE function, replace "A" with the specific character that you want to count. Alpha charaters need to be enclosed in quotes, numbers do not. This is case sensitive! Better yet, use a cell to hold the character that you want to count and use that cell reference in the SUBSTITUTE function. Biff -----Original Message----- I have a table of customers by row with the orders in one column. Thus a single cell may contain ordered items represented as "A", "B", "C", etc. or "1", "2", "3", etc. So Jill could have an order "B,B" which would represent and order for "two item B's" Sue's order is C,1,2,2 - where Sue wants 1 item "C", 1 item "1" and 2 item "2's". How can these "items" be tabulated for the total worksheet order to list a total for all the item "A's", "B's", etc and the items "1's", "2's", etc. Thanks for any help - for the future we need to see if the worksheet could be designed to comply with the lab's request to have the order listed in a single cell and still be able to compile a tabulation for invoicing purposes. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do you make some characters in a cell bold and some not? | Excel Discussion (Misc queries) | |||
#### error if cell has more than 255 characters | Excel Discussion (Misc queries) | |||
count in cell | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions | |||
how to count the nr of occurrences of a text string in a cell rang | Excel Worksheet Functions |