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 more 1's beside of eachother.
In the example below there is 3 1's beside of eachother and then it should show me YES or something in the cell at the end where the number 3 is. 3 is a counter of how many 1s is present.
1 is when there is critical cleaning issues. 0 in the end is non critical.
| Eksempel | Eksempel | Eksempel | Eksempel | kritisk | Belægninger | 1 | 1 | 1 | 3 | 0 |
Thanks for help in advance.
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).
- 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
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.
