I have created a spreadsheet used to track gas mileage. Each row
contains the date of fill-up, amount to fill up, pricer per gallon,
miles and then calculates mileage.

There is a separate section that calculates the average mileage per
month along with the total miles per month. Up until now I have always
selected a range of cells and used average and sum for each, but I
thought there should be a more automated way. More automated meaning
that each cell in the average mileage and total miles section will
determine what the month was entered in each row and put the calculated
mileage into the average and add the total miles to the sum.

The way I came up with was set up an two additional tables: one for
mileage and one for miles. Each table has a column for each month. The
first cell in the first column of the first table checks the first
entries date, if it matches it references the mileage for that entry.
The second cell of the first column checks second entry and so on. Each
cell in both tables does this check. The bottom of each column
calculates average and sum which is referenced by the monthly section.

Initially I was hoping a single formula could be used in each cell of
the monthly section to accomplish the same task. Is there a more
compact way to get the same results?

Thanks for any help,
GeoK

George wrote:
I have created a spreadsheet used to track gas mileage. Each row
contains the date of fill-up, amount to fill up, pricer per gallon,
miles and then calculates mileage.

There is a separate section that calculates the average mileage per
month along with the total miles per month. Up until now I have always
selected a range of cells and used average and sum for each, but I
thought there should be a more automated way. More automated meaning
that each cell in the average mileage and total miles section will
determine what the month was entered in each row and put the calculated
mileage into the average and add the total miles to the sum.

The way I came up with was set up an two additional tables: one for
mileage and one for miles. Each table has a column for each month. The
first cell in the first column of the first table checks the first
entries date, if it matches it references the mileage for that entry.
The second cell of the first column checks second entry and so on. Each
cell in both tables does this check. The bottom of each column
calculates average and sum which is referenced by the monthly section.

Initially I was hoping a single formula could be used in each cell of
the monthly section to accomplish the same task. Is there a more
compact way to get the same results?

Thanks for any help,

Hi GeoK,

This might work for you...

I created a table in A:C with columns labeled as
Dates ... Miles ... Gallons

I filled these columns with random values. Then, I created named ranges
for each column (for the sake of simplifying the formulae, as you will see).

In Column F, I placed dates like this, to represent months:
1/1/2008
2/1/2008
3/1/2008 etc. (m/d/yyyy)

In Column G, to obtain the total Miles by month:
=SUMPRODUCT(--($F2=DATE(YEAR(Dates),MONTH(Dates),1)),Miles)

In Column H, to obtain the total Gas by month:
=SUMPRODUCT(--($F2=DATE(YEAR(Dates),MONTH(Dates),1)),Gals)

In Column I, to obtain the average MPG by month, simply
=G2/H2