Countif, nestedif and sumproduct not able to populate the correct data

Iron Contributor

I need some guidance

 

i have got 3 workbooks; export, current workbook and credit limit request workbook.

 

The logic are as follows:

1. if Business Registration data in both export and credit limit request workbook exist, perform following formula:

(1a)

IFNA(IF(VLOOKUP(B1,[export.xlsx]Sheet1!E:E,1,0)=VLOOKUP(B1,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$B,1,0),SUMIF([export.xlsx]Sheet1!E:I,B1,Sheet1!I:I)

 

else

(1b)
VLOOKUP(B1,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$M,12,0),SUMIF(Sheet1!E:I,B1,[export.xlsx]Sheet1!I:I))

 

2. if Business Registration data does not exist in export but exist in credit limit request workbook with duplicate Business Registration id, perform following formula:

 

(2a)

IF(COUNTIF('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$B,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!B2>1),SUMPRODUCT(MAX(--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B1000=B1)*--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S1000=SUMPRODUCT(MAX(--('[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B2:$B1000=B1)*'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$S2:$S1000)))*'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$M2:$M1000))

 

else

VLOOKUP(B1,'[Credit Limit Requests (no existing SAP IDs).xlsx]No SAP ID'!$B:$M,12,0)

 

Formula 1a and 1b is working fine but formula 2a and 2b does not populate the correct data. 

End of the day i would like to combine both formula 1 and 2 into a single cell as an nested if.

 

Thanks in advance!

0 Replies