Formula to calculate sum/average based on number of unique dates in range

[JennOlsen]

I need to calculate an sum/average, however the divisor may be different based on the number of unique dates for each person.

Column H has the total hours for the given period.
Column G has the check dates for the employee.

So basically I need something like... =sum((H10/(# unique dates in range G5:G9))/2)

I'm trying to calculate the average # of hours being worked by employees each week. Since the report will also include those who have worked here for a time less than my report range, I need to account for that. We're paid biweekly, so I can only see 2 weeks worth of info at a time, thus why ultimately it then needs to be divided by 2 as the VP wants it based on a weekly #. I would prefer not to have to calculate the # of checks by hand.

Thanks for helping.

[Tony Valko]

For the count of unique dates in the range G5:G9...

=SUM(--(FREQUENCY(G5:G9,G5:G9)>0))

[JennOlsen]

For the count of unique dates in the range G5:G9...

=SUM(--(FREQUENCY(G5:G9,G5:G9)>0))

Did I do it wrong? =SUM((H10/(FREQUENCY(G5:G9,G5:G9)>0))) It gave me a #DIV/0!

[Tony Valko]

Try it like this...

=H10/SUM(--(FREQUENCY(G5:G9,G5:G9)>0))

[JennOlsen]

Try it like this...

=H10/SUM(--(FREQUENCY(G5:G9,G5:G9)>0))

That works. Too bad I can't make those ranges update for each of my lists.

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.