IF Formula with 3 Conditions

[ScottishPeter]

HI All,


Hoping for a little hand, getting late and mind isnt working.
I have a simple data set where I’m trying to set up some IF formulas.

Where:
If any cell in columns B, C, D, E are above 0 , And Column F is Blank, then it returns Lapsed in Column G
However if column F isn’t Blank it should return Not Lapsed.

But the problem I’m hitting is that columns B, C, D & E could also be blank, but Column F is populated, If this is the case it should return Not Lapsed as well.

Hope that makes sense.

B	C	D	E	F	G
Sum of Apr-23	Sum of May-23	Sum of Jun-23	Sum of Jul-23	Sum of Aug-23	Lapsed?
			0.2​		Lapsed
		2.0​	2.1​	0.4​	Not Lapsed
0.1​		0.1​	0.2​		Lapsed
3.0​	4.8​	2.5​	4.7​	6.6​	Not Lapsed
				0.9​	Not Lapsed
11.8​	6.4​	10.6​	10.7​	10.8​	Not Lapsed
3.5​	7.0​	2.5​	3.5​		Lapsed

 

[dreid1011]

How about this:

Book1
	A	B	C	D	E	F	G
1		Sum of Apr-23	Sum of May-23	Sum of Jun-23	Sum of Jul-23	Sum of Aug-23	Lapsed?
2					0.2		Lapsed
3				2.0	2.1	0.4	Not Lapsed
4		0.1		0.1	0.2		Lapsed
5		3.0	4.8	2.5	4.7	6.6	Not Lapsed
6						0.9	Not Lapsed
7		11.8	6.4	10.6	10.7	10.8	Not Lapsed
8		3.5	7.0	2.5	3.5		Lapsed
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=IF(AND(COUNTA($B2:$E2),ISBLANK($F2)),"Lapsed","Not Lapsed")

 

[DanteAmor]

This is what you need?

varios 26sep2023.xlsm
	A	B	C	D	E	F	G
1		Sum of Apr-23	Sum of May-23	Sum of Jun-23	Sum of Jul-23	Sum of Aug-23	Lapsed
2					0.2		Lapsed
3				2	2.1	0.4	Not Lapsed
4		0.1		0.1	0.2		Lapsed
5		3	4.8	2.5	4.7	6.6	Not Lapsed
6						0.9	Not Lapsed
7		11.8	6.4	10.6	10.7	10.8	Not Lapsed
8		3.5	7	2.5	3.5		Lapsed
9						1	Not Lapsed
10							Not Lapsed
Hoja6

Cell Formulas
Range	Formula
G2:G10	G2	=IF(AND(SUM(B2:E2)>0,F2=""),"Lapsed","Not Lapsed")

 

[ScottishPeter]

How about this:

Book1
	A	B	C	D	E	F	G
1		Sum of Apr-23	Sum of May-23	Sum of Jun-23	Sum of Jul-23	Sum of Aug-23	Lapsed?
2					0.2		Lapsed
3				2.0	2.1	0.4	Not Lapsed
4		0.1		0.1	0.2		Lapsed
5		3.0	4.8	2.5	4.7	6.6	Not Lapsed
6						0.9	Not Lapsed
7		11.8	6.4	10.6	10.7	10.8	Not Lapsed
8		3.5	7.0	2.5	3.5		Lapsed
Sheet1

Cell Formulas
Range	Formula
G2:G8	G2	=IF(AND(COUNTA($B2:$E2),ISBLANK($F2)),"Lapsed","Not Lapsed")

Perfect thanks

 

[dreid1011]

Perfect thanks

You're welcome.