AverageIFS, only filtered data

[Mark_Allen]

So I have my AverageIFS function working correctly, but now I need the values to update as I filter data.

[Pepe Le Mokko]

Hi and welcome
there is anice example at http://www.exceluser.com/excel_help/...-aggregate.htm using the AGGREGATE function

[avk]

Please Login or Register  to view this content.

This is example as per your data

[ChemistB]

I put a helper column in Column CG
CG2 copied down

Please Login or Register  to view this content.

I chose CF because it seems to have a value for every row. If there is a better column for that, choose that one.
This formula will show a 1 when the row is unfiltered and 0 when it is filtered out (hidden)
Then modify your AVERAGEIFS to include this column. For example BW21

Please Login or Register  to view this content.

[Mark_Allen]

Thanks for the replies!

Couple items; Chemist

1. I don't understand how your fix works. Do I need to apply this to each column or am I to reference only the new column you added?
1. My original data set will have 1000's of rows, there will be no single column with "perfect" data. Will this still work?
2. When I filter, my "total" row disappears.

[ChemistB]

1. Re Filtering: Clear the current filter and just select your data without the totals and reactivate the filter. It won't include the totals then.

2. You just need to refer to column CG in your AverageIFs. If there is no specific column that will have data in it, modify CG2 copied down to

Please Login or Register  to view this content.

[Mark_Allen]

Holy crap it works! Thanks a ton. You have no idea how much time this will save me.