Help needed with function

[Kevin]

Hello,

I keep a list of records that come in on a daily basis in a worksheet
called 'Records'. The date is in the first column and other information
about that record is in columns 2 through 10. On any particular day I
might have several records, but I don't have entries every day.

On another worksheet, I have functions which total up the number of
records that come in each month. This total is computed using equations
like this (this example is for January 2006):

=SUMPRODUCT(--(MONTH('Records'!$A$6:$A$10007)=1),--(YEAR('Records'!$A$6:$A$10007)=2006))

(I rec'd help from this newsgroup for this function)

What I would now like to do is add another function that calculates the
average records per day, but using the number of days that records were
rec’d, NOT the total number of days in the month. So, if a total of 93
records were rec’d on 10 days in January 2005, the total would be
divided by 10, NOT 31, giving a daily average of 9.3. This number for
the denominator can be arrived at by finding the number of “unique”
dates matching the particular month from column 1 of the master
worksheet, but I don’t know how to do this.

Can anyone help me with this?

Thanks,

Kevin

 

[Guest]

there will only be a date if you have received data??But you receive more
than one data sets per day?

 

[Biff]

Hi!

Do you need to specify the year as part of the criteria?

In the sample formula posted you use the year.

This formula will count the unique date entries for Jan 2006:

Array entered using the key combo of CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF((MONTH(A1:A20)=1)*(YEAR(A1:A20)=2006),MATCH(A1:A20,A1:A20,0)),MATCH(A1:A20,A1:A20,0))>0))

So, to get the average, just combine the 2 formulas: (still array entered)

=SUMPRODUCT(--(MONTH(A1:A20)=1),--(YEAR(A1:A20)=2006))/SUM(N(FREQUENCY(IF((MONTH(A1:A20)=1)*(YEAR(A1:A20)=2006),MATCH(A1:A20,A1:A20,0)),MATCH(A1:A20,A1:A20,0))>0))

No error checking!

Biff