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).
- I got it working. I am from Denmark so I needed to change some things: =HVIS(SUMPRODUKT(G230:I230;H230:J230;I230:K230);"Yes";"No").
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:N230
