Forum Discussion
Solved
Count if the number 1 is present 3 times or more beside eachother
Hi I am working with trend analysis in a cleaning company. My question is if there is a count if formula which can help me count trend. So I have the data below. I want to count if there is 3 or...
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).
Suppose you want to find out how many times particular text or a number value occurs in a range of cells.
Count how often a value occurs
- It is sort of the idea. But when you have 10 cells. It is like (% counts as empty cells): 1, 1, 1, %, %, %, 1, %, %, %.
And then I want to count if there is 3 times 1 has occured.
Because there can be a lot of rows in our spreadsheets and we only wanna show a yes when there is three or more 1's in a row.
