Got a bit of a conundrum I've been wracking my brain on for far to long and was wondering if anyone could help.
I have a list of items in column A and columns labelled as weekly periods from B:DA
| Item Code | Week 1 | Week 2 | Week 3 | Week 4 | Week 5 | Week 6 | Week 7 | Week 8 | Results |
|---|---|---|---|---|---|---|---|---|---|
| Item 1 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 | 3 |
| Item 2 | 1 | 1 | 0 | 0 | 1 | 1 | 1 |
I need to count the number of times the weekly status goes from 1 to 0 but not from 0 to 1.
In the tabled example I would expect the results to be Item 1 = 3 and Item 2 = 1
Any help pointing me in the right direction would be much appreciated!
CodePudding user response：
Use COUNTIFS():
=COUNTIFS(B2:CZ2,1,C2:DA2,0)
The offset ranges will allow the count of when the prior cell is 1 and the following is 0.
