| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count total month per year | Excel Discussion (Misc queries) | |||
| averaging a total out over a month | Excel Discussion (Misc queries) | |||
| I'm using the mileage log., How do I total all the mileage? | New Users to Excel | |||
| 12 month Rolling Total | Excel Worksheet Functions | |||
| chart: mileage MPG Fuel total paid | Charts and Charting in Excel | |||
Linear Mode
