Applying formulas to a table of filtered results

Hopefully this is aquite a straight forward question:

I have a long list of data for individual companies of which i will be filtering based upon industry groups. I then want to apply functions (finding the correlation between two company variables within a set industry group) to only the filtered results (i.e a specific industry type). At the moment the result of my formula is not affected by any filters placed on the data.

Does anyone know of an easy way to impliment this?

Any input greatly appreciaited,

Regards,
Tom

I am applying the CORREL(array1,array2) function to the data - and upon applying filters to the data i want this formula to automatically update.

I assumed there would be a simple way (i.e. non vba) to do this but may be wrong....

Thanks

The CORREL formula can not be used directly on a filtered list. The only function that works on a filtered list is the SUBTOTAL function. This can do calculations on a singel column. There are 11 different calculations that can be done. I don't know if the result from the CORREL function can be made by combining some SUBTOTAL function, but use the help function in Excel and you will se what calculations can be made.

If this don't help you must use "Advanced filter" instead of "Auto filter" and copy the result to a different place. Then you can use CORREL directly on the result.

Yeh i ended up using vba in the end to apply the formula on a copied set of the filtered results.
Thanks for your help.
Tom