Formulas, for filtered worksheet

P

Paul S

I am using Excel 2000

I have a spreadsheet with numerous columns, Column A has an area
indicator from 1 thro 10, Column G contains the names of staff or shows
vacant, Column H shows the number of hours they work and Column Z shows
a ranking based on Sales in Column Y

At the end of Column H I have the following formula
=SUBTOTAL(9,H4:H175), which when all areas are showing returns the
Total Hours worked, or if a filter is applied in column A will show the
number of hours worked for the area selected

Now here's where I need help, in Column G I have the following formula
=COUNTIF(G4:G174,"Vacant"), which counts the Total Number of Vacancies,
the value does not change if I apply a filter in column A

What formula would I need to use so that when I filter on column A it
only shows the vacancies for the selected Area

In Column Z I have the following formula =RANK(Y4,$Y$4:$Y$175,0)) again
the values do not change if I apply a filter in column A

What formula would I need to use so that when I filter on column A it
only shows the rankings for the selected Area

Thx in advance for any help
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cumulative formula for word table 0
Using Filter more than once 2
Need Array Formula for Counting Blank & Non Blank Cells (Multiple Criteria) 3
VLookup or Macro? 0
Compute Additional Hours Needed Using "if" or "lookup" 1
countif on multiple criteria 1
Excel functions 3
Changing column reference for number of formulas at once 1

Top