How to calculate averages and totals based on yearly and monthly ranges?

...or do i need (if it's even possible) to pass a DATE RANGE value to the "lookup_value" argument of the vector method LOOKUP function?
not sure how to properly describe the problem i'm having here, and i have not found any threads addressing this particular issue. if there is, i apologize.

i'm running office 2003 pro on windows xp sp3.

it began when i made this single-sheet workbook to keep track of my car's gas mileage. pretty straightforward. i record every fill up by entering in the 5 column "fill in section":
date, mileage, trip mileage, gallons taken, and money spent.

3 more columns automatically calculate as follows:
dollars per gallon, miles per gallon, and while i was at it, dollars per mile (i was bored i guess, idk).

at this point, i felt it could do more, so i tried to figure a yearly/monthly/weekly fuel budget for miles driven, gallons used, and money spent, as well as average miles per gallon for the year, month, etc (the weekly figure can just divide the monthly figure by 4).
i've tried the manual approach with absurdly nested IF statements, i've tried LOOKUP using the vector method, but i can't properly pass the month or year of the date column to the lookup_value...

this seems to be my problem/question in general:
do i need (or is it possible) to pass a DATE RANGE value to the "lookup_value" argument?

i can imagine it's WAY more simple and basic than i'm making this out to be. and for that, i apologize. i imagine it would look like i don't have the vaguest clue what i'm doing. it's quite possible i'm simply missing something so simple and so painfully obvious.
any help, hints, or general advice here would be tremendously and thoroughly appreciated.
if there's anything i might have left out, please let me know. many thanks in advance for bearing with me. ~

Originally Posted by exceleratorstuck

...or do i need (if it's even possible) to pass a DATE RANGE value to the "lookup_value" argument of the vector method LOOKUP function?

still messing with trial and error here, coming to a stark realization that i should at least start by using VLOOKUP instead of LOOKUP, although i'm still stuck when it comes to that lookup_value.

so for cell I4 (or R4C9) which is miles driven for the year 2010, i got as far as:

Please Login or Register  to view this content.

i'm trying anything to create that date range, and i'm starting to wonder if i need to use some random separate cell to collect the cells or rows that match the date range with a COUNTIF or something.... but i'm likely drawing at straws here.

Originally Posted by DonkeyOte

I'd suggest you use a Pivot Table - see attached.

DonkeyOte, i must thank you for it appears to me that you have not only solved my problem but you have shown me just how much i have yet to understand, while at the same time showing me a whole new world of what's possible.
i had heard of pivot tables, but everything i read up on about them only confused me more than helped me. it's clear to me now that i need to persevere, and that formulas are not always the go-to answer for everything. who knew
you are the greatest! thank you so much for your time, knowledge, experience, and of course, showing me the light. ~