Forum Discussion
Count matching cells diagonally
I'm looking for a formula.
I want to count cells diagonally that match the contents of the first cell. I want it to stop counting as soon as it reaches a cell that doesn't match.
I hope this is making sense
I want to count cells diagonally that match the contents of the first cell. I want it to stop counting as soon as it reaches a cell that doesn't match.
I hope this is making sense
10 Replies
- Hello, it is impossible to diagonally count cells.
You can use COUNTIFS to count values that meet multiple criteria- Thanks. That explains why I was struggling so much.
If I have a row of cells
AAABBAA
How do I achieve a result of 3A? And not 5A? So it counts until it finds a letter that's not AFor the data structured like this
to count in row
=AGGREGATE(15,6,1/($A$1:$G$1<>$A$1)*COLUMN($A$1:$G$1),1)-1to count in diagonal
=AGGREGATE(15,6,1/(INDEX($A$1:$G$7,TRANSPOSE(ROW($A$1:$A$7)),COLUMN($A$1:$G$1))<>$A$1)*COLUMN($A$1:$G$1),1)-1Please check in attached file.
One more point, actually we don't count, we find position of first not matched cell. If all cells are the same formula doesn't work, but could be easily corrected for such case wrapping by ISERROR returning number of cells in the range in case of error.
If range starts not from A1, formula are to be adjusted with rows/columns arithmetic e.g. not ROW($A$1:$A$7) but (ROW($A$11:$A$17)-ROW($A$11)+1), etc.
