Formula to find if any cells below A2 are equal to A2 and if so how many?

[suprsnipes]

Is it possible to use a formula to look at up 20 rows to see how many rows are equal to the first from the top down?

An example;
Column A row 1: formula to look at the 20 rows below to see if cell A2 is equal to A3, A4, A5 and so on and if so return the value of how many cells are equal to A1 if any.

I would really appreciate any help with this.

Regards,
suprsnipes

 

[hiker95]

suprsnipes,


Maybe something like this?

Excel Workbook
	A	B
1	6	4
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	6
10	10
11	11
12	12
13	6
14	14
15	15
16	16
17	17
18	18
19	19
20	6
21	21
22
Sheet1

 

[suprsnipes]

Thank you for the response. What I want to do is return not how many are the same but how many are in sequence. Would this be possible also?

 

[Matty]

Not 100% clear to me. Could you provide a small example - as well as the expected result - so that we know what you're after?

Matty

 

[hiker95]

suprsnipes,


Click on the Post Reply button, and just enter the word BUMP, and click on the Submit Reply button, and one of the formula Gurus will assist you.

 

[suprsnipes]

Hi,

My apologies.

This is what I am after. Ok, so the formula I need should be in B1. I want to find how many cells are equal to A1 but they must be in sequence, in the example below there are 4 in a row, so I want the result of 4. Even if there are more 6's below for example not in cell A5 but in A6 the answer should still be 4.

Realised now I probably should have explained myself better.

Sheet1

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:right; ">6
</td><td style="background-color:#ffff00; text-align:right; ">4</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">6
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">6
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">6
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td style="text-align:right; ">1</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td style="font-weight:bold; text-align:right; ">7
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td style="text-align:right; ">2
</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td style="text-align:right; ">8</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td style="font-weight:bold; text-align:right; ">5
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td style="text-align:right; ">10</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td style="text-align:right; ">11</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">12</td><td style="text-align:right; ">12</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">13</td><td style="font-weight:bold; text-align:right; ">3
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">14</td><td style="text-align:right; ">14</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">15</td><td style="text-align:right; ">15</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">16</td><td style="text-align:right; ">16</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">17</td><td style="text-align:right; ">17</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">18</td><td style="text-align:right; ">18</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">19</td><td style="text-align:right; ">19</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">20</td><td style="font-weight:bold; text-align:right; ">2
</td><td>
</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">21</td><td style="text-align:right; ">21</td><td> </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">22</td><td> </td><td> </td></tr></tbody></table>
Regards,
suprsnipes

 

[T. Valko]

Hi,

My apologies.

This is what I am after. Ok, so the formula I need should be in B1. I want to find how many cells are equal to A1 but they must be in sequence, in the example below there are 4 in a row, so I want the result of 4. Even if there are more 6's below for example not in cell A5 but in A6 the answer should still be 4.

Realised now I probably should have explained myself better.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">6


</TD><TD style="BACKGROUND-COLOR: #ffff00; TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">6


</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">6


</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">6


</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">1</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">7


</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">2


</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">8</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">5


</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">10</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">11</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">12</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">3


</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</TD><TD style="TEXT-ALIGN: right">14</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</TD><TD style="TEXT-ALIGN: right">15</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD style="TEXT-ALIGN: right">16</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="TEXT-ALIGN: right">17</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">18</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">19</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="FONT-WEIGHT: bold; TEXT-ALIGN: right">2


</TD><TD>


</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">21</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD></TD><TD></TD></TR></TBODY></TABLE>
Regards,
suprsnipes

In other words, you want to know the longest streak of consecutive cells that =A1?

Try this array formula:

=MAX(FREQUENCY(IF(A1:A22=A1,ROW(A1:A22)),IF(A1:A22<>A1,ROW(A1:A22))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[suprsnipes]

Hi,

I thought the suggested formula below would work and whilst it is helpful it's not exactly what I am after.

I want to know what the current streak is.

<table border="0" cellpadding="0" cellspacing="0" width="91"><colgroup><col style="mso-width-source:userset;mso-width-alt:1024;width:24pt" width="32"> <col style="mso-width-source:userset;mso-width-alt:992;width:23pt" width="31"> <col style="mso-width-source:userset;mso-width-alt:896;width:21pt" width="28"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;width:24pt" height="20" width="32">A1</td> <td class="xl64" style="border-left:none;width:23pt" align="right" width="31">6</td> <td class="xl65" style="border-left:none;width:21pt" align="right" width="28">3
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">A2</td> <td class="xl64" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl63" style="height:15.0pt;border-top:none" height="20">A3</td> <td class="xl64" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A4</td> <td class="xl63" style="border-top:none;border-left:none" align="right">5</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A5</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A6</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A7</td> <td class="xl63" style="border-top:none;border-left:none" align="right">7</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A8</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A9</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A10</td> <td class="xl63" style="border-top:none;border-left:none" align="right">5</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A11</td> <td class="xl63" style="border-top:none;border-left:none" align="right">4</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A12</td> <td class="xl63" style="border-top:none;border-left:none" align="right">3</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A13</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A14</td> <td class="xl63" style="border-top:none;border-left:none" align="right">1</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A15</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A16</td> <td class="xl63" style="border-top:none;border-left:none" align="right">7</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A17</td> <td class="xl63" style="border-top:none;border-left:none" align="right">8</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A18</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A19</td> <td class="xl63" style="border-top:none;border-left:none" align="right">6</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl63" style="height:14.25pt;border-top:none" height="19">A20</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> <td class="xl63" style="border-top:none;border-left:none">
</td> </tr> </tbody></table>

In other words, you want to know the longest streak of consecutive cells that =A1?

Try this array formula:

=MAX(FREQUENCY(IF(A1:A22=A1,ROW(A1:A22)),IF(A1:A22<>A1,ROW(A1:A22))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Can it be done?

Regards,
suprsnipes

 

[energman58]

I think if you simply delete the MAX from T Valko's formula that will do it.

 

[suprsnipes]

Brilliant, thanks very much to all who contributed!