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.
.