# find rows after counting data

#### jts2004

##### Board Regular
In a range A1:A100 the number 3 shows up 10 times. I have a formula to count all the 3's in the range now I want to know what rows they are on.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### hatman

##### Well-known Member
In cell B!: =if(a1=3,row(a1),"")
Copy down to cell b100

In cell C1: =if(iserror(small(\$B\$1:\$B\$100,row(1:1)),"",small(\$B\$1:\$B\$100,row(1:1))
Copy down to cell c100.

#### hatman

##### Well-known Member
OR:

In cell B1: =MATCH(3,A1:A100,FALSE)
and in cell B2: =IF(ISERROR(MATCH(3,INDIRECT("A"&B1+1&":a100"),FALSE)),"",MATCH(3,INDIRECT("A"&B1+1&":a100"),FALSE)+B1)

copy B2 to B100.

This only works if your data actually starts at A1... if it starts on a lower row, you need a more general case.

##### MrExcel MVP
jts2004 said:
In a range A1:A100 the number 3 shows up 10 times. I have a formula to count all the 3's in the range now I want to know what rows they are on.
Book1
ABCD
143
252
365
477
53
68
73
87
99
Sheet1

C1 is used to specify the entry of interest.

C2:

=COUNTIF(A1:A9,C1)

Determines C1's frequency of occurrence in the target range.

C3:

=IF(ROW()-ROW(C\$3)+1<=\$C\$2,SMALL(IF(\$A\$1:\$A\$9=\$C\$1,ROW(\$A\$1:\$A\$9)),ROW()-ROW(C\$3)+1),"")

which is confirmed with control+shift+enter then copied down, determines the native row location per occurrence.

#### Yogi Anand

##### MrExcel MVP

Hi jts2004:

Book1
ABCD
1a33
2b6
338
41
52
63
77
83
9g
10
Sheet1

array formula in cell B1:B3 is ...

=SMALL(IF(A1:A10=3,ROW(A1:A10)),ROW(INDIRECT("1:"&C1)))

#### jts2004

##### Board Regular
Good stuff

Those all work great. Thanks

##### MrExcel MVP
Re: Good stuff

jts2004 said:
Those all work great. Thanks

They are all different. And that matters...

Replies
3
Views
305
Replies
9
Views
310
Replies
1
Views
74
Replies
9
Views
179
Replies
3
Views
186