Hi, could you please send a sample of your data, thanks

"mePenny" wrote:

It's telling me i have a divide by zero error #DIV/0

"Eduardo" wrote:

Hi,
In that case try

=SUMPRODUCT((A6:A100=2009)*(B6:B100="u"),C6:C100)/SUMPRODUCT((A6:A100=2009)*(B6:B100="u"))

=SUMPRODUCT((A6:A100=2009)*(B6:B100="d"),C6:C100)/SUMPRODUCT((A6:A100=2009)*(B6:B100="d"))

"mePenny" wrote:

EXCEL 2002 SP3

"Eduardo" wrote:

Hi,
I tested it and it's working , are you using excel 2007 ?
"mePenny" wrote:

This tells me #NAME? for both

"Eduardo" wrote:

Hi,

=AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"u")
=AVERAGEIFS(C6:C100,A6:A100,"2009",B6:B100,"d")


"mePenny" wrote:

I have the following formula's to seperate out fuel (u=unleaded, d=diesel)
for Fiscal (7/1/09 - 6/30/09) and Calendar year. I'm looking for an average
of gallons of fuel formula for the Fiscal and Calendar year. Can anyone help?

mePenny

Calendar
=SUMPRODUCT((YEAR(A6:A100)=2009)*(B6:B100="u")*C6: C100)
=SUMPRODUCT((YEAR(A6:A101)=2009)*(B6:B101="d")*C6: C101)

Fiscal
=SUMPRODUCT(((YEAR(A6:A100)=2009)*(MONTH(A6:A100) =7)+(YEAR(A6:A100)=2010)*(MONTH(A6:A100)<=6))*(B6: B100="u")*C6:C100)

=SUMPRODUCT((TEXT(A6:A100,"yyyymm")="200907")*(TE XT(A6:A100,"yyyymm")<="201006")*(B6:B100="d")*C6:C 100)