Combining 'counta' and 'countif'

Hi all,

I'm trying to create a report to show daily statistics for internet traffic.

I have a large table showing a daily breakdown of performance, a table showing combined cost for all "Mondays", "Tuesdays" etc in the month and I also want a table to show the "average" performance for a typical "Monday", "Tuesday" etc.

The report will be used on a daily basis and I realised that until there is a full compliment of data the daily averages table would be inaccurate as it will be dividing the overall total for each day by the total number of occurances each day has in a typical month. This is wrong because if we are only halfway through the month then we will be dividing by days that haven't arrived yet.

I thought about applying a "counta" function to the formula but this doesn't work so thought I'd post here for advice.

The formula I have (without the counta) is:

Please Login or Register  to view this content.

When I applied the "counta" I did so like this:

Please Login or Register  to view this content.

What this formula does when the counta is applied is having the same effect as there being nothing after the "/" and it is just adding all of the "Monday" stats together.

Would appreciate any help. I think this is beyond my capabilities!

Hi,

Does this work for you?

=SUMIF('Report Template'!$C$8:$C$38,"=Monday",'Report Template'!D$8:D$38)/SUMPRODUCT(--(C8:C38="Monday")*--(D8:D38>0))

Glad to hear it - thanks for the feedback