Multiple IF Criteria

[blenderwala]

I got a task in which i have 2 Files

One is a Master File containing the names of the clients and the EMI amount with Payment 1 detail
And there is Another file containing Daily Collection with payment 2 Detail

In Master File there are two column

Pay 1
Pay 2(Values Coming From Another File Via VLookUp)

If someone pays both his EMIs at once, then that name might not be in the second list.

What I Need to Calculate is:

If one pays both his Total EMI Amount (EMI value *2) , his status should be shown as PAID.

If someone pays only one EMI then his status should be shown as HALF.

And if someone doesn't make any payments, his/her status should be shown as UNPAID.


__________________________

Master.xlsx
	A	B	C	D	E
1	Name	EMI	Pay-1	Pay-2	STATUS
2	Ajay	500	500	500
3	Vijay	700	1000	#N/A
4	Priyanka	300	0	300
5	Rohit	1000	0	0
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=VLOOKUP(A2,'C:\Users\Nidhi\Desktop\Excel Problems\Main Issue\[Daily.xlsx]Sheet1'!$A:$D,3,0)

Daily.xlsx
	A	B	C	D
1	Name	EMI	Pay-2	STATUS
2	Sumit	500	0
3	Priyanka	700	300
4	Rohit	1000	0
5	Ajay	500	500
Sheet1

___________________________________

Any Help will be very very appreciated

Thank You.

 

[jtakw]

Hi,

I'm not sure I understand entirely.
So if Column D has #N/A, then that's "PAID" whether or not there's a positive number in C, or vice versa ??

Book3.xlsx
	A	B	C	D	E
1	Name	EMI	Pay-1	Pay-2	STATUS
2	Ajay	500	500	500	PAID
3	Vijay	700	1000	#N/A	PAID
4	Priyanka	300	0	300	HALF
5	Rohit	1000	0	0	UNPAID
Sheet1086

Cell Formulas
Range	Formula
E2:E5	E2	=IFNA(IF(AND(C2,D2),"PAID",IF(OR(C2,D2),"HALF","UNPAID")),"PAID")

 

[blenderwala]

Thank you so much @jtakw sir for you reply

I am very sorry but there is just one mistake in that file :
Vijay's EMI Amount is 500 not 700
Hence his total due amount is 500 * 2 = 1000,
He paid Entire EMI Amount (1000= 500*2) in one go. That's why his name is not in Another File hence it is showing #N/A

Master.xlsx
	A	B	C	D	E
1	Name	EMI	Pay-1	Pay-2	Expected STATUS
2	Ajay	500	500	500	Paid
3	Vijay	500	1000	#N/A	Paid
4	Priyanka	300	0	300	Half
5	Rohit	1000	0	0	Unpaid
6	Suraj	200	200	#N/A	Half
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=VLOOKUP(A2,'C:\Users\Nidhi\Desktop\Excel Problems\Main Issue\[Daily.xlsx]Sheet1'!$A:$D,3,0)

Let's Say
Tom's EMI is 500
Tom has to pay two EMIs
So his total due is 500*2 =1000,

If he pays those 1000 in one go(from Pay-1 or Pay -2( Value coming from another file via VLOOKUP) ) then his status should be shown as PAID
(Pay 1 or Pay 2 =1000)

If he pays 1000 in two terms Pay1+Pay2(VLOOKUP) then his status should be shown as PAID
(Pay 1 +Pay 2 =1000)

If he just pays one EMI 500 (Either from Pay 1 or Pay2(VLOOKUP) then status should be Half
(Pay 1 +Pay 2 =500)

And If he don't pay anything , then his status should be Unpaid
(Pay 1+ Pay 2 =0)




Again Thank you for your guidance

 

[jtakw]

Thanks for the additional info.

If you can change your current D column formula to:

=IFERROR(VLOOKUP(A2,'C:\Users\Nidhi\Desktop\Excel Problems\Main Issue\[Daily.xlsx]Sheet1'!$A:$D,3,0),0)

Just adding IFFEROR(original formula,0)

Then the calculation for STATUS is much simpler:

Book3.xlsx
	A	B	C	D	E
1	Name	EMI	Pay-1	Pay-2	STATUS
2	Ajay	500	500	500	Paid
3	Vijay	500	1000	0	Paid
4	Priyanka	300	0	300	Half
5	Rohit	1000	0	0	Unpaid
6	Suraj	200	200	0	Half
Sheet1086

Cell Formulas
Range	Formula
E2:E6	E2	=IF(C2+D2=B2*2,"Paid",IF(C2+D2,"Half","Unpaid"))

 

[blenderwala]

Thank you so much @jtakw sir

this is what i wanted to get


Although I made just a small change in the file, instead of using VLOOKUP i used SUMIF ,
so if a name appears twice in that second list it will get the sum


Again, Thanks a lot. The problem is solved

 

[jtakw]

You're welcome, thanks for the feedback.