If Statement- Multiple Conditions

[pto160]

I am trying to write an If statement for multiple conditions that is a bit tricky. I am trying to write a condition in column E that says Yes or No. I want to include this in a pivot table report. The condition is when the type is equal to credit and the date is equal to or greater than 2/1/22 then I want a yes in column E, but to also include the original type= sale that could have occurred before 2/1/22.
So in this case for example for invoice 100, there is a credit that occurred at 2/5/22, but there is a SALE that occurred on 12/13/21 so both would be yes. Here is what I am looking for.

Book1
	A	B	C	D	E
1					What I want
2	Date	Type	Invoice #	Amount	Include
3	12/13/2021	Sale	100	15	Yes
4	1/15/2022	Credit	200	30	No
5	2/5/2022	Credit	100	10	Yes
6	1/1/2022	Sale	200	50	No
7	2/10/2022	Sale	300	60	Yes
8	2/16/2022	Credit	300	20	Yes
Sheet2

 

[earthworm]

Try below . I am confused with orange highlighted invoice i think you wrote the answer incorrect.

Also note that The date format on my PC is : DD-MM-YYY and you are using MM-DD-YYY . Please Make changes accordingly.

Book1
	A	B	C	D	E	F
1					What I want
2	Date	Type	Invoice #	Amount	Include
3	13-Dec-21	Sale	100	15	Yes	Yes
4	15-Jan-22	Credit	200	30	No	No
5	5-Feb-22	Credit	100	10	Yes	Yes
6	1-Jan-22	Sale	200	50	No	Yes
7	10-Feb-22	Sale	300	60	Yes	Yes
8	16-Feb-22	Credit	300	20	Yes	Yes
Sheet1

Cell Formulas
Range	Formula
F3:F8	F3	=IF(AND(B3="Credit",A3>("1-2-2022")*1),"Yes",IF(AND(B3="Sale",A3<("2-1-2022")*1),"Yes",IF(AND(B3="Sale",A3>("2-1-2022")*1),"Yes","No")))

 

[Ezguy4u]

pto because you can never have too many solutions, here is mine. Now like earth I have to wonder about Cells E6 but also E7. So let the discission begin.

22-03-02 if.xlsx
	A	B	C	D	E	F	G
1					What I want
2	Date	Type	Invoice #	Amount	Include
3	12/13/2021	Sale	100	15	Yes		Yes
4	1/15/2022	Credit	200	30	No		No
5	2/5/2022	Credit	100	10	Yes		Yes
6	1/1/2022	Sale	200	50	Yes		No
7	2/10/2022	Sale	300	60	No		Yes
8	2/16/2022	Credit	300	20	Yes		Yes
Data

Cell Formulas
Range	Formula
E3:E8	E3	=IF(OR(AND(A3>=44593,B3="Credit"),AND(A3<=44593,B3="Sale")),"Yes","No")

 

[pto160]

Thanks for responding.
For the response to earthworm, the credit for invoice 200 is Jan 15 2022, which is before Feb 1 2022, so the credit and sale will be "No".
For the response to Ezguy4u , the credit for invoice 300 occurred equal to or greater than February 1, 2022, so the credit and sale is yes.
To summarize, if the credit is equal or greater than 2/1/22, it is a Yes and the sale is also a Yes, no matter when that SALE occurs.

 

[jtakw]

Hi,

This should do what you want:

Book3.xlsx
	A	B	C	D	E
1					What you want
2	Date	Type	Invoice #	Amount	Include
3	12/13/2021	Sale	100	15	Yes
4	1/15/2022	Credit	200	30	No
5	2/5/2022	Credit	100	10	Yes
6	1/1/2022	Sale	200	50	No
7	2/10/2022	Sale	300	60	Yes
8	2/16/2022	Credit	300	20	Yes
Sheet1031

Cell Formulas
Range	Formula
E3:E8	E3	=IF(COUNTIFS(A$3:A$8,">=2/1/22",B$3:B$8,"Credit",C$3:C$8,C3),"Yes","No")

 

[pto160]

Thanks jtakw. It works fantastic. This will really help me with this report at work.
I was trying to combine this formula you provided me with another IF statement, where the invoice has to have both a sale and credit as well.
There are some invoices that have just a credit since I am running this report on a date range and the sale could have occurred before that date range.
E10 will be a NO since there is no SALE.

Book1
	A	B	C	D	E
1					What you want
2	Date	Type	Invoice #	Amount	Include
3	12/13/2021	Sale	100	15	Yes
4	1/15/2022	Credit	200	30	No
5	2/5/2022	Credit	100	10	Yes
6	1/1/2022	Sale	200	50	No
7	2/10/2022	Sale	300	60	Yes
8	2/16/2022	Credit	300	20	Yes
9	2/15/2022	Sale	400	50	No
10	2/16/2022	Credit	500	100	Yes
Sheet1

Cell Formulas
Range	Formula
E3:E10	E3	=IF(COUNTIFS(A$3:A$10,">=2/1/22",B$3:B$10,"Credit",C$3:C$10,C3),"Yes","No")

 

[jtakw]

Hi,

Try this modified version for your new requirement:

Book3.xlsx
	A	B	C	D	E
1					What you want
2	Date	Type	Invoice #	Amount	Include
3	12/13/2021	Sale	100	15	Yes
4	1/15/2022	Credit	200	30	No
5	2/5/2022	Credit	100	10	Yes
6	1/1/2022	Sale	200	50	No
7	2/10/2022	Sale	300	60	Yes
8	2/16/2022	Credit	300	20	Yes
9	2/15/2022	Sale	400	50	No
10	2/16/2022	Credit	500	100	No
Sheet1031

Cell Formulas
Range	Formula
E3:E10	E3	=IF(AND(COUNTIFS(B$3:B$10,"Sale",C$3:C$10,C3),COUNTIFS(A$3:A$10,">=2/1/22",B$3:B$10,"Credit",C$3:C$10,C3)),"Yes","No")

 

[pto160]

This formula works great.?
Thanks so much for your help. This will save me a lot of time.

 

[jtakw]

You're welcome, thanks for the feedback.