| 1 | 4 | 1 |
|---|---|---|
| 2 | 3 | 3 |
| 3 | 1 | 2 |
| 4 | 4 | 3 |
| 5 | 3 | 2 |
| 6 | 2 | 1 |
| 7 | 4 | 4 |
| 8 | 2 | 1 |
| 9 | 3 | 4 |
| 10 | 2 | 2 |
| Reading 11 | 1 | 1 |
| 12 | 2 | 3 |
| 13 | 3 | 3 |
| 14 | 3 | 2 |
| 15 | 4 | 1 |
| 16 | 1 | 3 |
| 17 | 2 | 2 |
| 18 | 2 | 2 |
| 19 | 3 | 2 |
| 20 | 4 | 4 |
Variations of the formula I've been trying.
=arrayformula(sum(("C2"&":"&ADDRESS(MATCH("Reading*",$A$2:$A$20,0),3,1)=$B$1:$B$10)*1))
=COUNTIF("C1"&":"&ADDRESS(MATCH("Reading*",$A$1:$A$20,0),3,1),$B$1:$B$10)
=SUMIF("C1"&":"&ADDRESS(MATCH("Reading*",$A$1:$A$20,0),3,1),$B$1:$B$10)
I am trying to count the cells in column C that match column B up until the word "Reading* shows up. "Reading*" will change cells often so it is just easier to sheets count it than me. My current issue is the formula is always returning 0.
So the expected result for the formula should be 3 since rows 2, 7 and 10 match.
This finds the offset from A2 of the first instance of 'Reading', subtracts one row, uses this to limit the array comparison of columns B & C to the same range and then counts the number of TRUEs.
=arrayformula(let(lastRow,xmatch("Reading*",A2:A,2)-1,countif(offset(B2,0,0,lastRow)=offset(C2,0,0,lastRow),true)))