formula for referencing dates that change

Hope someone can help

I get a file of data every week that contains info on the 7 days in that week. There is a column that specifies the date next to all of the other responses.

At the moment, to work out the average of a set of responses for each day I have to use the filters

Is there any formula that I can use which would automatically seperate data based on dates?

For the other columns I use a combination of macro's and cell formula but of course the dates change every week so not sure how to do this

Any help appreciated

Can you post an example of the file?

You can use a pivot table. You can also check out formulas like SUMIF, COUNTIF, and AVERAGEIF. If you provide more detail or a dummy workbook, we can provide a better answer.

Thanks for help guys

Much appreciated

Have attached an example of what I get. In coumn C you will see scores on a scale of 1 - 5
For purposes of reporting I need to work out the % that score either 4 or 5 but I have to do this for each day of the week which takes ages

Any formula I use wont work as teh dates change each week

Again, any help is massively appreciated

Perhaps like this. The formulas used will update the week every week and take into account the possibility of varying numbers of results.

Sorry, I missed the point about percentages. You can use

Please Login or Register  to view this content.

in F2 and copied down.

mdbct, your formula doesn't take into account what happens if there are no values for the corresponding dates. It's fine if the dates are inputted manually every week, but if the data changes or they are automatically entered, it will cause a #DIV/0! error.

I thought by this statement in his OP

There is a column that specifies the date next to all of the other responses.

that the dates were available for use.

I get that. In my edition, I tried to take out the manual entering of dates. I also have had it drilled into me by the mods here (and in work experience) that the one time you don't account for the unexpected change is the time it's sure to happen.
Also, you can combine your Sumproduct statements either as I have or by using $C$2:$C$51={4,5}.

Thanks for this bit "...combine your Sumproduct statements either as I have or by using $C$2:$C$51={4,5}