Forum Discussion
Count if the number 1 is present 3 times or more beside eachother
Jakob2022 You can use SUMPRODUCT of overlapping ranges.
Lets assume the range start on G1 and goes to K1 then use
=IF(SUMPRODUCT(G1:I1,H1:J1,I1:K1),"YES","no")by using the 3 ranges offset by 1 each, this sumproduct will multiply each set of 3 together and only get 1 if there are 3 next to each other and 0 every other time and then the sum will be >0 if it find at least 1 set of 3 (and will be more if there is more than 1 set of 3 or more together).
Jakob2022 You can use SUMPRODUCT of overlapping ranges.
Lets assume the range start on G1 and goes to K1 then use
=IF(SUMPRODUCT(G1:I1,H1:J1,I1:K1),"YES","no")by using the 3 ranges offset by 1 each, this sumproduct will multiply each set of 3 together and only get 1 if there are 3 next to each other and 0 every other time and then the sum will be >0 if it find at least 1 set of 3 (and will be more if there is more than 1 set of 3 or more together).
If I then had from G to N to check how would it be then? mtarler
- Glad it is working for you. The key is to offset the ranges by 1 so they would start at G, H, I and go to L, M, N. something like:
G230:L230, H230:M230, I230:N230mtarler I'm not sure if this is the way:
=HVIS(SUMPRODUKT(G2:I2;H2:J2;I2:K2;J2:L2;K2:M2;L2:N2);"Yes";"No")
It doesn't work as intended. Your code with less cells works still. I will check from G2 to N2.
Thanks in advance.
- if you have columns 1 to 100 then you want 1 to 98 , 2 to 99, 3 to 100 so if the columns are G2 to N2 you want G2:L2, H2:M2, I2:N2