If statement

[ekhawaja]

Looking for an if statement to check 5 column for value of "1", and if ALL have 1, return "1", otherwise "0"

 

[dreid1011]

Something like this:

=IF(COUNTIF(A2:E2,1)=5,1,0)

 

[ekhawaja]

Thank you so much for the reply - SORRY i should have been more clear, what about this scenario, where the values are not in the adjacent columns. Formula to be in col K.

 

[dreid1011]

Are there any values in between the columns which can contain a 1?

 

[Fluff]

As well as the question by dreid1011, will you only have 1s & 0s in those columns, or could you have other numbers?

 

[ekhawaja]

Are there any values in between the columns which can contain a 1?

yes, they can contain a 1, but i don't want to check those.

 

[dreid1011]

A couple of options for you then, in Columns L and M which specifically look at every other column from A to I.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										IF	FLOOR	MAX/SUM	IF/SUM
2	1	2	1	3	1	4	1	5	1	1	1	1	1
3	1	1	1	0	0	0	1	1	1	0	1	0	0
4	1				0		1		1	0	0	0	0
Sheet4

Cell Formulas
Range	Formula
J2:J4	J2	=IF(COUNTIF(A2:I2,1)=5,1,0)
K2:K4	K2	=FLOOR.MATH(COUNTIF(A2:I2,1)*0.2,1)
L2:L4	L2	=MAX(SUM(INDEX(A2:I2,,SEQUENCE(1,5,1,2)))-4,0)
M2:M4	M2	=IF(SUM(INDEX(A2:I2,,SEQUENCE(1,5,1,2)))=5,1,0)

I suppose I should have waited for your answer to Fluff's question whether the cells you want to count contain other values than 1 or 0... it may change the solution offered.

 

[ekhawaja]

A couple of options for you then, in Columns L and M which specifically look at every other column from A to I.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1										IF	FLOOR	MAX/SUM	IF/SUM
2	1	2	1	3	1	4	1	5	1	1	1	1	1
3	1	1	1	0	0	0	1	1	1	0	1	0	0
4	1				0		1		1	0	0	0	0
Sheet4

Cell Formulas
Range	Formula
J2:J4	J2	=IF(COUNTIF(A2:I2,1)=5,1,0)
K2:K4	K2	=FLOOR.MATH(COUNTIF(A2:I2,1)*0.2,1)
L2:L4	L2	=MAX(SUM(INDEX(A2:I2,,SEQUENCE(1,5,1,2)))-4,0)
M2:M4	M2	=IF(SUM(INDEX(A2:I2,,SEQUENCE(1,5,1,2)))=5,1,0)

I suppose I should have waited for your answer to Fluff's question whether the cells you want to count contain other values than 1 or 0... it may change the solution offered.

i will only have 1 or 0.

 

[ekhawaja]

As well as the question by dreid1011, will you only have 1s & 0s in those columns, or could you have other numbers?

i will only have 1 or 0.

As well as the question by dreid1011, will you only have 1s & 0s in those columns, or could you have other numbers?

i will only have 1 or 0.

 

[dreid1011]

i will only have 1 or 0.

Then either option in columns L or M should do what you are after. If not, let us know.