=sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006),($G$2:$G$1000-$F$2:$F$1000))/sumproduct(--(Month($G$2:$G$1000)=10),--(Year($G$2:$G$1000)=2006))


You can replace the 10 and 2006 with references to cells that contain 10 and
2006 (or other month year combination).

--
Regards,
Tom Ogilvy




"FCC(SW)JP USN" wrote in message
...
Hey Tom appreciate the quick response. Everyone in G from G2 through G1000
have a detach date. The detach dates go from 2003-present. On a summry
sheet
I have to display the average days onboard by month and year. Column H has
the number of days the individual was here, my end result needs to be
something along the lines of average H2:H1000 if G2:G1000,"yyyymm"=200610
but
I don't quite no how to make it work. I have read many articles, and tried
various Ideas utilizing SUMPRODUCT and Average if, but am not having any
luck.

JP

"Tom Ogilvy" wrote:

Assuming everyone in G849 to G880 has a detach date:
= (sum(G849:G880)-sum(H849:H880))/rows(G849:G880)

Format the cell with the formula as General (it might default to looking
like a weird date).

--
Regards,
Tom Ogilvy



"FCC(SW)JP USN" wrote:

Trying to figure out how to automatically avearge the number of days by
the
month they dparted. I could manually do this each month by using the
avearge
function for instance =AVERAGE(H849:H880) but how can I set up a
formula to
avearge the total days based on the yyyymm of the detach date. Any help
would
be great.
F G H
Report date Detach date Total days
878 29-Aug-06 26-Sep-06 27
879 22-Aug-06 27-Sep-06 35
880 12-Sep-06 29-Sep-06 17
881 6-Sep-06 2-Oct-06 26
882 6-Sep-06 2-Oct-06 26
883 8-Aug-06 3-Oct-06 55
884 6-Sep-06 3-Oct-06 27