Forum Discussion
Count matching cells diagonally
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 A
For 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.
- Ah that worked great! Thankyou so much
Not sure how to wrap the iserror
And how would I find where the b cells start and count them? On a row (I've given up with diagonal)Hayles739 , you are welcome.
You may handle errors like
=IFERROR(AGGREGATE(15,6,1/($A$1:$G$1<>$A$1)*COLUMN($A$1:$G$1),1)-1, <do something else if an error in main formula>)As for finding B cell and counting them I didn't catch what exactly do you mean. Initial requirement was to count how many times is repeated first cell in the sequence.
For better understanding it will be better if you submit sample file with entered manually desired result.
Sorry I had to go out, I've added an example of what I'm looking for with the answers I hope you can find a formula for. Hope that helps to clarify what I''m trying to do? and thankyou so much for all the help so far