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
|