How to use autofilter and average together? or something similar.

[kostas]

Hello all,

I have the following problem. As you can see in the attached spreadsheet I have a table with a number of columns that have different values.

One of the columns has values of the days of the week.

What I want to do is the following, get the average of the values of each day of the week, for each column. How can I do that with a formula?

I have tried using MATCH, SUBTOTALS etc but could not manage to get anything different other than the overall average.

In the real spreadsheet I have there are about 13000 lines where the days of the week are repeated, so I need something that can find all the records of a specific day and return me the average of all the values (column by column) that relate to that day.

Any help appreciated.

Regards,

Kostas


p.s I have tried the =sumif(x:x,"text",s:s)/countif(x:x,"text")
It seems be working really strange cause I only get extremely low values which shouldn't be right,

at the end of each column I also have a total average of the column, but when I use the sumif/countif trick it includes the average at the bottom in the new calculation. Is there a way to avoid that without specifying a range of rows for each column? (probably something with the autofilter)

cheers