Hello,
I am trying to find an excel formula which would help me count the number of times a certain value has appeared in one row.
The tricky part is that the counting should only start after the 3rd time it has appeared on that same row.
For example, on the table below I would like to count how many times the number "5" has appeared consecutively only after it has appeared for the 3rd time.
For this example, counting of the number "5" will start on cell E1 and will therefore count as 4x (cell value at B3).
| A | B | C | D | E | F | G | H | I | |
| 1 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 1 |
| 2 | 1 | 5 | 5 | 5 | 5 | 1 | 1 | 5 | 5 |
Row 1 consecutive "5" = 4x (E1, F1, G1, and H1)
Row 2 consecutive "5" = 2x (D2, and E2)
Am not sure if any of the formulas in excel will be able to compute given this criteria.
Have managed to count the occurrence the number "5" has consecutively appeared using below formula but have failed to add the condition of starting the count only after the 3rd appearance.
=SUMPRODUCT((B1:I1=A1:H1)*(B1:I1=5))
So I hope to hear from you guys real soon.