automatically adjusting formulas on filtering

Hi,

I have a spreadsheet with the following column headings:

Date, Quarter, Type of Facility, Address, State, Region, 1.1, 1.2, 1.3,...1.10, 2.1........9.8.

Each of the numbered columns (1.1, 1.2 etc) correspond to a particular issue. These numbered columns are filled with "YES", "NO" or "NA" (not applicable) for the corresponding location. I have simple counting formulas that sum the number of No's, Yes's and NA's for each column, and also a formula that calculates percentages. These calculations are displayed at the bottom of the workshhet below all of the data

Ideally I would like to filter the spreadsheet so that I can analyse the data by any combination of Quarter (1Q, 2Q, 3Q or 4Q), Type of Facility, or Region (East, West, North, or South).

However, when I apply a filter, although the data is filtered the way I want it to be, the formulas that sum the columns do not automatically adjust to calculate totals for just the filtered data. Is there a way to have these formuals automatically adjust after filtering?

Mark

Originally Posted by NBVC

Try something like:

Please Login or Register  to view this content.

where A1:A3 or the items you want to count based on B1:B3 equalling "Yes"...

adjust ranges to suit.

My skill in excel is pretty basic, and so are the formulas I work with. I don't fully understand what you mean by "where A1:A3 or the items you want to count based on B1:B3 equalling "Yes"

For example, in my spreadsheet, I am summing column J, rows 8 - 111 so my formula is =COUNTIF(J8:J111,"yes"). How would I do this using the code you suggested?

Thanks. And what is the "1" and "3" for in your formula?