Forum Discussion
Formula to count a value appearing consecutively 2 or more times in multiple columns
Hi There,
I am looking for a formula, basically to count a string value appearing consecutively for 2 or more times in multiple columns. Have got an idea, however not sure about the implementation part. I have manually done it below, but a formula would really help a lot.
| Consecutive Pass | ||||||||||
| Day1 | Day2 | Day3 | Day4 | Day5 | Day6 | once | twice | thrice | ||
| Case1 | Pass | Fail | Pass | Fail | Pass | Pass | Yes | |||
| Case2 | Pass | Pass | Fail | Pass | Pass | Pass | Yes | Yes | ||
| Case3 | Fail | Pass | Pass | Pass | Pass | Fail | Yes | Yes | Yes | |
| Case4 | Pass | Fail | Pass | Pass | Pass | Pass | Yes | Yes | Yes | |
| Case5 | Pass | Pass | Fail | Pass | Pass | Pass | Yes | Yes | ||
| Case6 | Pass | Pass | Pass | Pass | Pass | Pass | Yes | Yes | Yes | |
| Case7 | Pass | Pass | Pass | Fail | Pass | Pass | Yes | Yes | ||
| Case8 | Pass | Pass | Fail | Pass | Pass | Pass | Yes | Yes | ||
| Case9 | Pass | Fail | Pass | Pass | Pass | Pass | Yes | Yes | Yes | |
| Case10 | Pass | Pass | Pass | Fail | Pass | Pass | Yes | Yes |
Any help on this front would be really appreciated. Please do let me know if any more details required.
Thanks
Mohsin
- The first test can be achieved with a formula like this (assuming you are using Excel 2016):
=IF(ISERROR(FIND("Pass|Pass",TEXTJOIN("|",FALSE,B2:G2))),"No","Yes")
The Twice column needs a similar formula:
=IF(ISERROR(FIND("Pass|Pass|Pass",TEXTJOIN("|",FALSE,B2:G2))),"No","Yes")
And the Thrice column:
=IF(ISERROR(FIND("Pass|Pass|Pass|Pass",TEXTJOIN("|",FALSE,B2:G2))),"No","Yes")
