I have a file that doesn't take up many columns, but it's 7000 lines
long. I own the Master and I have 10 people that each have their own
copy. They make updates to it weekly and I transfer the information
from their file to mine. I keep a Summary tab to help me understand
the progress we are making towards our goal.

The main file contains filters along each column. One of the columns
contains Warehouse locations. One column contains a drop down menu
(validation) in which the user can chose either Hold or Pull. Finally,
another column contains three choices: Large, Medium, or Small.

I need to track how each warehouse location is doing with their Holds
and Pulls, so I filter on them and see what I need. I also change the
cell from Hold to Pull if their file tells me to do so. I'm trying to
make a summary tab that will update this information for me. The
problem I'm running into is when I filter to see Warehouse #1, the row
numbers are no longer go from 1 - 7000 in strick numerical order. They
might go 1, 2, 5, 30, 31, 35, 50, etc. When I try and use a Countif
Excel assumes I want to count rows 1 thru 7000, when I'm only
interested in Warehouse #1.

This is a rather long post, but I wanted to give as much information
as I thought was needed in order to help me.

Thank you.

Chip

Try this:

Row 1 are column headers with filter applied.

B2:B7000 = column filtered on "warehouse".

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B7000,ROW(B2:B700 0)-ROW(B2),0,1)),--(B2:B7000="warehouse"))

--
Biff
Microsoft Excel MVP


wrote in message
oups.com...
I have a file that doesn't take up many columns, but it's 7000 lines
long. I own the Master and I have 10 people that each have their own
copy. They make updates to it weekly and I transfer the information
from their file to mine. I keep a Summary tab to help me understand
the progress we are making towards our goal.

The main file contains filters along each column. One of the columns
contains Warehouse locations. One column contains a drop down menu
(validation) in which the user can chose either Hold or Pull. Finally,
another column contains three choices: Large, Medium, or Small.

I need to track how each warehouse location is doing with their Holds
and Pulls, so I filter on them and see what I need. I also change the
cell from Hold to Pull if their file tells me to do so. I'm trying to
make a summary tab that will update this information for me. The
problem I'm running into is when I filter to see Warehouse #1, the row
numbers are no longer go from 1 - 7000 in strick numerical order. They
might go 1, 2, 5, 30, 31, 35, 50, etc. When I try and use a Countif
Excel assumes I want to count rows 1 thru 7000, when I'm only
interested in Warehouse #1.

This is a rather long post, but I wanted to give as much information
as I thought was needed in order to help me.

Thank you.

Chip