To criteria with Reference from Cell

[PERANISH]

I having some datas in one column 9n many rows. i want to take reference in
some letters fot SUMPRODUCT FUNCTION

EXAMPLE

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

I WANT FORMULA IF COL-A CONTAINS - X - SUM COL-B
LIKE, IF COL-A CONTAINS - XA - SUM COL-B
LIKE, IF COL-A CONTAINS - XXA - SUM COL-B

WHETHER I CAN USE WITH SUMPRODUCT FUNCTION.

AWAITING YOUR HELP PLEASE.
THANKS
PERANISH

 

[Stefi]

Make a list in C2:C4
C
2 X
3 XA
4 XXA

Insert this formula in D2 and fill it down to D4:

=SUMPRODUCT(--(C2=LEFT($A$2:$A$7,LEN(C2))),$B$2:$B$7)

But be careful! Your question is amiguous! If a string contains e.g. XXA
then it also contains X and XA!

Regards,
Stefi


„PERANISH†ezt írta:

 

[Teethless mama]

c1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-1)
copy down to c6

d1: =SUMPRODUCT((C1:C6={"X","XA","XXA"})*B1:B6)

 

[PERANISH]

Dear Sirs,

Thanks for your reply & that formula works on well.

Sorry to say that, my datas having small change as below
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 "

ONCE AGAIN SORRY FOR THE INCONVEIENCE.

AWAITNG YOUR HELP PLESE