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

 

[energman58]

Or You could use

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

Which is more obvious why it works - still entered as an array formula

 

[T. Valko]

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 cellSpacing=0 cellPadding=0 width=91 border=0><COLGROUP><COL style="WIDTH: 24pt; mso-width-source: userset; mso-width-alt: 1024" width=32><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 992" width=31><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 896" width=28></COLGROUP><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="WIDTH: 24pt; HEIGHT: 15pt" width=32 height=20>A1</TD><TD class=xl64 style="BORDER-LEFT: medium none; WIDTH: 23pt" align=right width=31>6</TD><TD class=xl65 style="BORDER-LEFT: medium none; WIDTH: 21pt" align=right width=28>3

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=20>A2</TD><TD class=xl64 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=20>A3</TD><TD class=xl64 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A4</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>5</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A5</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A7</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>7</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A8</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A9</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A10</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>5</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A11</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>4</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A12</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>3</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A13</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>2</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A14</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>1</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A15</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A16</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>7</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A17</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>8</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A18</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A19</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>6</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 14.25pt" height=19>A20</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>2</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR></TBODY></TABLE>



Can it be done?

Regards,
suprsnipes

Ok, does that mean the "current streak" starts from the top of the data range?

If so, try this...

Book1
	A
1	6
2	6
3	6
4	100
5	6
6	6
7	6
8	6
9	6
10	6
11	6
12	6
13	6
14	6
15	6
16	6
Sheet1

Array entered**:

=FREQUENCY(IF(A1:A16=A1,ROW(A1:A16)),IF(A1:A16<>A1,ROW(A1:A16)))

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