I have a Excel 2007 table linked to an external data source that has a column that shows a value anywhere from 0 up to around 600. This column is sorted in ascending order and ther will always be filters on other columns in the table. Also, the # of records in this table can change. This is on a sheet called Raw Data.

On another sheet, I ideally need to show the sum of the top 80% of the values in this column, while only looking at the records that have been filtered. It would be great to have this done in 1 formula.

If doing this with 1 formula is not an option, I thought I could count the # of records showing after my filters in 1 cell (for example 285). Then I can calculate 80% of this count in another cell (example 228). Then I can use a variant of the formula below (where D3 is the cell with the calculated 80% count).

=SUMPRODUCT(LARGE(A:A,ROW(INDIRECT("1:" & D3))))

I actually have this working with the calculations on the same sheet as the column and when there is no filter.

Any help would be greatly appreciated.

Thanks!