Working with filtered data and IFs

[Thapelo]

I want to use the AVERAGEIF, SUMIF AND COUNTIF functions but want them to only apply to the visible data on a filtered table. When i use AVERAGEIF it applies to all the data in the table even when the filter is applied to the table. How do i get it to only apply to the visible data on the table?

 

[MarcelBeug]

Try SUBTOTAL or AGGREGATE (Excel 2010 and higher).

 

[vicedo]

can you paste a screenshot of your spreadsheet or make some samples in here using tables

 

[eliW]

Hi Thapelo and welcome to the board,
Look at SUBTOTAOL function and its arguments. this can help you to manipulate only filtererd cells by SUM, Average, or COUNT them.
All the best,

 

[Thapelo]

Hi Thapelo and welcome to the board,
Look at SUBTOTAOL function and its arguments. this can help you to manipulate only filtererd cells by SUM, Average, or COUNT them.
All the best,

I find the SUBTOTAL function to be a bit limiting in this case. I want to be able to average, sum or count values that match multiple criteria in a filtered list. When i use AVERAGEIFS it will only average the values in the range that match ALL the criteria in the formula. However, it considers ALL the data in the table. I want it to only apply the AVERAGEIFS to the data in the table after i have filtered the table.

 

[eliW]

OK
not sofisticated but effective: Copy the filtererd rows to another worksheet and work on them there.

 

[Thapelo]

Thank. Was hoping to avoid that but that seems to be the only option.

 

[MarcelBeug]

Why?

Table1 with columns Number and Value, both

=AGGREGATE(1,5,Table1[Value])
and 
=SUBTOTAL(101,Table1[Value])

give me the average of the fiiltered values.