Trying to get total mileage for a specific month
 

I have a spreadsheet which shows the date and mileage of business journeys. I
am trying to create a function that will for each month pick out the mileages
for that month and total them up.

Tried the following: =SUMIF(A19:A64,"=01/04/2006",G19:G64) where the A
column is the dates and the G column are the mileages, but I cant seem to set
up an upper limit for the dates. ie Apr journeys only, etc

Help

Big Chief

Trying to get total mileage for a specific month
 

Try this:

Total mileage for Jan 2007:
=SUMPRODUCT(TEXT(A19:A64,"YYYYMM"="200701")*G19:G6 4)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"big_chief_hutch" wrote in message
...
I have a spreadsheet which shows the date and mileage of business journeys.
I
am trying to create a function that will for each month pick out the
mileages
for that month and total them up.

Tried the following: =SUMIF(A19:A64,"=01/04/2006",G19:G64) where the A
column is the dates and the G column are the mileages, but I cant seem to
set
up an upper limit for the dates. ie Apr journeys only, etc

Help

Big Chief

Trying to get total mileage for a specific month
 

Ron

Sorry, still coming up with error message. The SUMIF function I used below
did total everything after 1st Apr 06 ok, but I couldn't put in an upper date
limit. I also tried using the MONTH function within it and also tried a
further less than 1st May 06, but always got an error message

Keith



"Ron Coderre" wrote:

Try this:

Total mileage for Jan 2007:
=SUMPRODUCT(TEXT(A19:A64,"YYYYMM"="200701")*G19:G6 4)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"big_chief_hutch" wrote in message
...
I have a spreadsheet which shows the date and mileage of business journeys.
I
am trying to create a function that will for each month pick out the
mileages
for that month and total them up.

Tried the following: =SUMIF(A19:A64,"=01/04/2006",G19:G64) where the A
column is the dates and the G column are the mileages, but I cant seem to
set
up an upper limit for the dates. ie Apr journeys only, etc

Help

Big Chief

Trying to get total mileage for a specific month
 

Oops! Typo! (sorry)

Try working with this:
=SUMPRODUCT((TEXT(A19:A64,"YYYYMM")="200701")*G19: G64)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"big_chief_hutch" wrote in message
...
Ron

Sorry, still coming up with error message. The SUMIF function I used below
did total everything after 1st Apr 06 ok, but I couldn't put in an upper
date
limit. I also tried using the MONTH function within it and also tried a
further less than 1st May 06, but always got an error message

Keith



"Ron Coderre" wrote:

Try this:

Total mileage for Jan 2007:
=SUMPRODUCT(TEXT(A19:A64,"YYYYMM"="200701")*G19:G6 4)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"big_chief_hutch" wrote in
message
...
I have a spreadsheet which shows the date and mileage of business
journeys.
I
am trying to create a function that will for each month pick out the
mileages
for that month and total them up.

Tried the following: =SUMIF(A19:A64,"=01/04/2006",G19:G64) where the A
column is the dates and the G column are the mileages, but I cant seem
to
set
up an upper limit for the dates. ie Apr journeys only, etc

Help

Big Chief

Trying to get total mileage for a specific month
 

Ron

Excellent - working a treat. Also seen this in another post, but yours I
prefer.

=SUMPRODUCT(--(MONTH(A19:A64)=10),--(YEAR(A19:A64)=2006),G19:G64)

Many thanks for all the help

Keith



"Ron Coderre" wrote:

Oops! Typo! (sorry)

Try working with this:
=SUMPRODUCT((TEXT(A19:A64,"YYYYMM")="200701")*G19: G64)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"big_chief_hutch" wrote in message
...
Ron

Sorry, still coming up with error message. The SUMIF function I used below
did total everything after 1st Apr 06 ok, but I couldn't put in an upper
date
limit. I also tried using the MONTH function within it and also tried a
further less than 1st May 06, but always got an error message

Keith



"Ron Coderre" wrote:

Try this:

Total mileage for Jan 2007:
=SUMPRODUCT(TEXT(A19:A64,"YYYYMM"="200701")*G19:G6 4)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"big_chief_hutch" wrote in
message
...
I have a spreadsheet which shows the date and mileage of business
journeys.
I
am trying to create a function that will for each month pick out the
mileages
for that month and total them up.

Tried the following: =SUMIF(A19:A64,"=01/04/2006",G19:G64) where the A
column is the dates and the G column are the mileages, but I cant seem
to
set
up an upper limit for the dates. ie Apr journeys only, etc

Help

Big Chief

Trying to get total mileage for a specific month
 

I'm glad I could help, Keith.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"big_chief_hutch" wrote in message
...
Ron

Excellent - working a treat. Also seen this in another post, but yours I
prefer.

=SUMPRODUCT(--(MONTH(A19:A64)=10),--(YEAR(A19:A64)=2006),G19:G64)

Many thanks for all the help

Keith



"Ron Coderre" wrote:

Oops! Typo! (sorry)

Try working with this:
=SUMPRODUCT((TEXT(A19:A64,"YYYYMM")="200701")*G19: G64)

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"big_chief_hutch" wrote in
message
...
Ron

Sorry, still coming up with error message. The SUMIF function I used
below
did total everything after 1st Apr 06 ok, but I couldn't put in an
upper
date
limit. I also tried using the MONTH function within it and also tried a
further less than 1st May 06, but always got an error message

Keith



"Ron Coderre" wrote:

Try this:

Total mileage for Jan 2007:
=SUMPRODUCT(TEXT(A19:A64,"YYYYMM"="200701")*G19:G6 4)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

"big_chief_hutch" wrote in
message
...
I have a spreadsheet which shows the date and mileage of business
journeys.
I
am trying to create a function that will for each month pick out the
mileages
for that month and total them up.

Tried the following: =SUMIF(A19:A64,"=01/04/2006",G19:G64) where
the A
column is the dates and the G column are the mileages, but I cant
seem
to
set
up an upper limit for the dates. ie Apr journeys only, etc

Help

Big Chief