No, it is not looking at column B. the "SEQUENCE(1,5,1,2)" portion of each of our formulas is telling it to look at every other column starting with column A.i think this works, this formula is only looking at every other column?
when i choose A2:I2 in the formula, is it not looking at column B ( i don't want to validate B)..
yes, they can contain a 1, but i don't want to check those.Are there any values in between the columns which can contain a 1?
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 will only have 1 or 0.A couple of options for you then, in Columns L and M which specifically look at every other column from A to I.
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.