I posted this prior with no final answer to my question.

I'm looking for a formula that can help sort calendar from fiscal year. I'm
hoping this is enough info. If not please let me know.

mePenny


HEADINGS ONLY:
A4 - DATE
B4 - TYPE OF FUEL (U = UNLEADED, D = DIESEL)
C4 - GALLON AMOUNT OF FUEL DELIVERED
D4 - PRICE PER GALLON AT TIME OF DELIVERY

ENTRIES ONLY:
A6 - A24 ARE THE ENTRIES OF FUEL DELIVERED

G5 - CALENDAR YEAR (HEADING ONLY)
G6 - (WORD ONLY) UNLEADED H6 - (NEED FORMULA HERE)
G7 - (WORD ONLY) DIESEL H7 - (NEED FORUMLA HERE)

G9 - FISCAL YEAR (HEADING ONLY)
G10 - (WORD ONLY) UNLEADED H10 - (NEED FORMULA HERE)
G11 - (WORD ONLY) DIESEL H11 - (NEED FORMULA HERE)

Okay, what i'm looking for is the formulas for cells H6,H7,H10,H11
H6,H7: I need to pull the amount of fuel delivered in the current calendar
year (January to December) for each type of fuel (H6 unleaded, H7 diesel)
H10,H11: I need to pull the amount of fuel delivered in the current fiscal
year (7/1/09 - 6/30/10) for each type of fuel (H10 unleaded, H11 diesel)

difficult without seeing the spreadsheet but sounds like you want to be
looking at DSum (if you are using excel 2003 or prior) or sumifs (if you are
using excel 2007).

if it is Dsum then you will need another little table something like in j1
to k2.
DATE DATE
7/1/09 <6/30/10

then =dsum(a4:d24,"GALLON AMOUNT OF FUEL DELIVERED",j1:k2)

if you want to split it by unleaded /diesel then add another column in L1
called TYPE OF FUEL (U = UNLEADED, D = DIESEL) and under it type unleaded and
then change the last reference to L2 not K2.

the bit in the quotes MUST be the exact same as your column headings in the
table of info (oh and one last thing - dont put a blank line under your
headings - not good practice).

sumifs does very similar thing to Dsum but contains all the criterias within
the function rather than having to create the separate table.

hope this helps

"mePenny" wrote:

I posted this prior with no final answer to my question.

I'm looking for a formula that can help sort calendar from fiscal year. I'm
hoping this is enough info. If not please let me know.

mePenny


HEADINGS ONLY:
A4 - DATE
B4 - TYPE OF FUEL (U = UNLEADED, D = DIESEL)
C4 - GALLON AMOUNT OF FUEL DELIVERED
D4 - PRICE PER GALLON AT TIME OF DELIVERY

ENTRIES ONLY:
A6 - A24 ARE THE ENTRIES OF FUEL DELIVERED

G5 - CALENDAR YEAR (HEADING ONLY)
G6 - (WORD ONLY) UNLEADED H6 - (NEED FORMULA HERE)
G7 - (WORD ONLY) DIESEL H7 - (NEED FORUMLA HERE)

G9 - FISCAL YEAR (HEADING ONLY)
G10 - (WORD ONLY) UNLEADED H10 - (NEED FORMULA HERE)
G11 - (WORD ONLY) DIESEL H11 - (NEED FORMULA HERE)

Okay, what i'm looking for is the formulas for cells H6,H7,H10,H11
H6,H7: I need to pull the amount of fuel delivered in the current calendar
year (January to December) for each type of fuel (H6 unleaded, H7 diesel)
H10,H11: I need to pull the amount of fuel delivered in the current fiscal
year (7/1/09 - 6/30/10) for each type of fuel (H10 unleaded, H11 diesel)

Craig, Thank you very much. I'll give this a try and let you know.

Happy Holidays

"Craig" wrote:

difficult without seeing the spreadsheet but sounds like you want to be
looking at DSum (if you are using excel 2003 or prior) or sumifs (if you are
using excel 2007).

if it is Dsum then you will need another little table something like in j1
to k2.
DATE DATE
7/1/09 <6/30/10

then =dsum(a4:d24,"GALLON AMOUNT OF FUEL DELIVERED",j1:k2)

if you want to split it by unleaded /diesel then add another column in L1
called TYPE OF FUEL (U = UNLEADED, D = DIESEL) and under it type unleaded and
then change the last reference to L2 not K2.

the bit in the quotes MUST be the exact same as your column headings in the
table of info (oh and one last thing - dont put a blank line under your
headings - not good practice).

sumifs does very similar thing to Dsum but contains all the criterias within
the function rather than having to create the separate table.

hope this helps

"mePenny" wrote:

I posted this prior with no final answer to my question.

I'm looking for a formula that can help sort calendar from fiscal year. I'm
hoping this is enough info. If not please let me know.

mePenny


HEADINGS ONLY:
A4 - DATE
B4 - TYPE OF FUEL (U = UNLEADED, D = DIESEL)
C4 - GALLON AMOUNT OF FUEL DELIVERED
D4 - PRICE PER GALLON AT TIME OF DELIVERY

ENTRIES ONLY:
A6 - A24 ARE THE ENTRIES OF FUEL DELIVERED

G5 - CALENDAR YEAR (HEADING ONLY)
G6 - (WORD ONLY) UNLEADED H6 - (NEED FORMULA HERE)
G7 - (WORD ONLY) DIESEL H7 - (NEED FORUMLA HERE)

G9 - FISCAL YEAR (HEADING ONLY)
G10 - (WORD ONLY) UNLEADED H10 - (NEED FORMULA HERE)
G11 - (WORD ONLY) DIESEL H11 - (NEED FORMULA HERE)

Okay, what i'm looking for is the formulas for cells H6,H7,H10,H11
H6,H7: I need to pull the amount of fuel delivered in the current calendar
year (January to December) for each type of fuel (H6 unleaded, H7 diesel)
H10,H11: I need to pull the amount of fuel delivered in the current fiscal
year (7/1/09 - 6/30/10) for each type of fuel (H10 unleaded, H11 diesel)

Craig,

Well unfortunately it didn't work. Can I send you the file? I understand a
bit about formula's but what your trying to explain is confusing, sorry.

Penny

"Craig" wrote:

difficult without seeing the spreadsheet but sounds like you want to be
looking at DSum (if you are using excel 2003 or prior) or sumifs (if you are
using excel 2007).

if it is Dsum then you will need another little table something like in j1
to k2.
DATE DATE
7/1/09 <6/30/10

then =dsum(a4:d24,"GALLON AMOUNT OF FUEL DELIVERED",j1:k2)

if you want to split it by unleaded /diesel then add another column in L1
called TYPE OF FUEL (U = UNLEADED, D = DIESEL) and under it type unleaded and
then change the last reference to L2 not K2.

the bit in the quotes MUST be the exact same as your column headings in the
table of info (oh and one last thing - dont put a blank line under your
headings - not good practice).

sumifs does very similar thing to Dsum but contains all the criterias within
the function rather than having to create the separate table.

hope this helps

"mePenny" wrote:

I posted this prior with no final answer to my question.

I'm looking for a formula that can help sort calendar from fiscal year. I'm
hoping this is enough info. If not please let me know.

mePenny


HEADINGS ONLY:
A4 - DATE
B4 - TYPE OF FUEL (U = UNLEADED, D = DIESEL)
C4 - GALLON AMOUNT OF FUEL DELIVERED
D4 - PRICE PER GALLON AT TIME OF DELIVERY

ENTRIES ONLY:
A6 - A24 ARE THE ENTRIES OF FUEL DELIVERED

G5 - CALENDAR YEAR (HEADING ONLY)
G6 - (WORD ONLY) UNLEADED H6 - (NEED FORMULA HERE)
G7 - (WORD ONLY) DIESEL H7 - (NEED FORUMLA HERE)

G9 - FISCAL YEAR (HEADING ONLY)
G10 - (WORD ONLY) UNLEADED H10 - (NEED FORMULA HERE)
G11 - (WORD ONLY) DIESEL H11 - (NEED FORMULA HERE)

Okay, what i'm looking for is the formulas for cells H6,H7,H10,H11
H6,H7: I need to pull the amount of fuel delivered in the current calendar
year (January to December) for each type of fuel (H6 unleaded, H7 diesel)
H10,H11: I need to pull the amount of fuel delivered in the current fiscal
year (7/1/09 - 6/30/10) for each type of fuel (H10 unleaded, H11 diesel)