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

Frequent Contributor

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

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.