Fill Series With Filter On

A

Alec Kolundzic

Can anyone tell me how I can fill a series with the filter
turned on.
I need to filter some data, then sequentially number
adjacent cells.

Thanks
Alec
 
J

Jason Morin

I can think of 2 ways, and here's the easiest: Prior to
filtering, use a formula to add a sequence of numbers. For
example, if you are filtering for the word "Fayette" in
col. B:

=IF(B2="Fayette",COUNTIF($B$2:B2,B2),"")

And copy down. Or if you're filtering on two columns
("Larry" in col. A and "Fayette" in col. B):

=IF(AND(A2="Larry",B2="Fayette"),SUMPRODUCT
(($A$2:A2="Larry")*($B$2:B2="Fayette")),"")

HTH
Jason
Atlanta, GA
 
D

Dave Peterson

How about putting the numbers in before you filter.

Can you pick a column in your filter that is always used?

I put my range to filter in A1:G30 (headers in row 1). Then in H2:h30, I put
this formula:

=SUMPRODUCT(SUBTOTAL(3,$A$2:OFFSET($A$2,ROW()-2,0)))
 
A

Alec Kolundzic

Thanks Dave
-----Original Message-----
How about putting the numbers in before you filter.

Can you pick a column in your filter that is always used?

I put my range to filter in A1:G30 (headers in row 1). Then in H2:h30, I put
this formula:

=SUMPRODUCT(SUBTOTAL(3,$A$2:OFFSET($A$2,ROW()-2,0)))





--

Dave Peterson
[email protected]
.
Click to expand...
 
A

Alec Kolundzic

Thanks Jason
-----Original Message-----
I can think of 2 ways, and here's the easiest: Prior to
filtering, use a formula to add a sequence of numbers. For
example, if you are filtering for the word "Fayette" in
col. B:

=IF(B2="Fayette",COUNTIF($B$2:B2,B2),"")

And copy down. Or if you're filtering on two columns
("Larry" in col. A and "Fayette" in col. B):

=IF(AND(A2="Larry",B2="Fayette"),SUMPRODUCT
(($A$2:A2="Larry")*($B$2:B2="Fayette")),"")

HTH
Jason
Atlanta, GA

.
Click to expand...
 
Top