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