Forum Discussion

Hayles739's avatar
Hayles739
Copper Contributor
Dec 21, 2019

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 maki...
excel
Abiola1's avatar
Abiola1
MVP
Dec 22, 2019
Hello, it is impossible to diagonally count cells.

You can use COUNTIFS to count values that meet multiple criteria
Hayles739's avatar
Hayles739
Copper Contributor
Dec 22, 2019
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 A
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 22, 2019

    Hayles739 

    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)-1

    to 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)-1

    Please 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.

    • Hayles739's avatar
      Hayles739
      Copper Contributor
      Dec 22, 2019
      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)
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Dec 22, 2019

        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.

Resources