sumif on concatenated lookup

[raju.ms]

Hi,

Could you please help me with the following issue.

I've a workbook in excel, in which I've sheet1,sheet2 and sheet3.

In sheet1 , I've

Column1 Column2

------------------

RM P101

RM P221

RM P311

SM P482

SM P574

SM P687

TM P798

TM P812

TM P909


In Sheet2, I've


Column1 Column2

--------------------

RM.P101 190

RM.P221 -120

RM.P311 -321

SM.P482 50

SM.P574 70

SM.P687 -234

TM.P798 456

TM.P812 655

TM.P909 -123


Now in Sheet3, I need a macro which will fetch the data from Sheet1 and Sheet2 as follows:

It will pick "RM" and "P101","P221""P311" as these are "RM" related from sheet1, and look for corresponding values from Sheet2. i.e., RM.P101's value 190, RM.P221's value -120 and RM.P311's value -321. Now we need to sum up all these values and show against "RM" if the sum is >0 els show 0 against "RM".


Thanks in Advance,

Raju.ms

 

[xld]

You can use a formula


=SUMPRODUCT(--(Sheet1!A1&"."&Sheet1!B1=Sheet2!$A$1:$A$20),Sheet2!$B$1:$B$20)

 

[raju.ms]

Hi,

Thanks for the solution. I've a slight change in my requirement.

In sheet1,

I've

Column1 Column2

------------------

RM P101

RM P221

SPM P574

SPM P687

TTTRM P798


In Sheet2, I've


Column1 Column2

--------------------

RM.P101 190

RM.P221 -120

RM.P311 -321

RM.P412 900

RM.766 -778

SPM.P482 50

SPM.P574 70

SPM.P687 -234

TTTRM.P798 456

TTTRM.P812 655

TTTRM.P909 -123


Now in Sheet3, I should display sum of RM,SPM, TTTRM for the values listed in Column2 of Sheet1 by referring to its concatenated string and their value.


For example,

Here in Sheet 3, we'll pickup column2 values for RM from Sheet1 (i.e., P101, P221) and lookup for corresponding values in Sheet2(i.e., 190 and 120) and show the sum if the sum is >0 els we'll show the sum as 0. So, we'll not consider the other values in sheet2 for RM (-321,900,778) as these are related to (P311,P412,P766) which are not listed in Sheet1.


Regards,

Raju ms

 

[xld]

Okay, try


=SUMPRODUCT(--(LEFT(Sheet2!$A$1:$A$20,LEN(A1))=A1),Sheet2!$B$1:$B$20)

 

[raju.ms]

Hi,

In sheet3 values should be


RM 70

SPM 0 (as the sum is a negative value)

TTTRM 456


So, I guess this formula is not sufficient for this. Please correct me if I'm wrong