To criteria with Reference from Cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PERANISH - 27 May 2008 09:31 GMT

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

Reply to this Message

Stefi - 27 May 2008 14:33 GMT

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:

> I having some datas in one column 9n many rows. i want to take reference in
> some letters fot SUMPRODUCT FUNCTION
[quoted text clipped - 18 lines]
> THANKS
> PERANISH

Reply to this Message

Teethless mama - 27 May 2008 15:26 GMT

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)

> I having some datas in one column 9n many rows. i want to take reference in
> some letters fot SUMPRODUCT FUNCTION
[quoted text clipped - 18 lines]
> THANKS
> PERANISH

Reply to this Message

PERANISH - 28 May 2008 09:56 GMT

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

> c1: =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-1)
> copy down to c6
[quoted text clipped - 23 lines]
> > THANKS
> > PERANISH

Reply to this Message