View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary''s Student Gary''s Student is offline
external usenet poster
 
Posts: 11,058
Default Request for formula to refer some letters from Cell

In cell C1 enter:
=LEN(A1)-LEN(SUBSTITUTE(A1,"X","")) and copy down. We see:

00XA10010253 5 1
00XXXA10012267 1 3
00XA20045682 2 1
00XXXA23564457 4 3
00XAA1000253 1 1
00XAA200000 3 1
00XXA106402A01 5 2

Column C "counts" the number of X's in column A. Then, elsewhe

=SUMPRODUCT(B1:B7,--(C1:C7=1)) sum column B single X's
=SUMPRODUCT(B1:B7,--(C1:C7=2)) sum column B double X's
=SUMPRODUCT(B1:B7,--(C1:C7=3)) sum column B three X's

--
Gary''s Student - gsnu200789


"PERANISH" wrote:

COL-A COL-B
00XA10010253 5
00XXXA10012267 1
00XA20045682 2
00XXXA23564457 4
00XAA1000253 1
00XAA200000 3
00XXA106402A01 5

I WANT IF COL-A = X , SUM COL-B
XX , SUM COL-B
XXX , SUM COL-B
PLEASE NOTE, I WANT AFTER THER LETTERS -BETWEEN FIRST 2DIGIT NUMBER(00) AND
EXCEPT -A
EXAMPLE IF 00XXXA200202A01 MEANS - I WANT" XXX "

AWAITNG YOUR HELP PLESE

-PER ANISH