| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum between date values
Good Day
I am currently using the below formula for min and max value by month(). I would like to go a step further now and recover the sum value between 2 date values. ie. diesel used between January to April per vehicle Appreciate the help "Robbie" wrote: Hi Pete Thank you for your help, the adding F1 Month will certainly help. Regards "Pete_UK" wrote: Use F1, say, to enter the month you are interested in (as a number 1 to 12), and list your registration numbers in column F starting with F2. Then in G2 you can put this array* formula to get the maximum: =MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100)) and this one in H2 to get the minimum: =MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10)) I have assumed that you have up to 100 rows of data - adjust if you have more. *Array formulae need to be committed using the key combination of CTRL- SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. If you amend the formula you must use CSE again. However, you can use the normal copy/paste operation to copy G2:H2 down to cover the number of vehicles you have. Change the number in F1 to choose a different month. Hope this helps. Pete On May 5, 11:57 am, Robbie wrote: I am trying to get a min and a max value in column C for the various Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED 1 1/5/08 JWY467GP 10000 1000 2 5/5/08 PTS030GP 5000 500 3 6/5/08 MVT537GP 2000 250 4 7/5/08 JWY467GP 12000 1000 5 10/5/08 PTS030GP 6000 500 6 12/05/08 MVT537GP 2500 250 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum between date values
Try with your query dates in E1 and F1
E1 StartDate F1 End Date Change the reference to the truck number or reference to a acell (all in one line) =SUMPRODUCT(--(B2:B100="TruckReg"),--(A2:A100=E1),--(A2:A100<=F1),D2:D100) (Adjust the range as required) If this post helps click Yes --------------- Jacob Skaria "Robbie" wrote: Good Day I am currently using the below formula for min and max value by month(). I would like to go a step further now and recover the sum value between 2 date values. ie. diesel used between January to April per vehicle Appreciate the help "Robbie" wrote: Hi Pete Thank you for your help, the adding F1 Month will certainly help. Regards "Pete_UK" wrote: Use F1, say, to enter the month you are interested in (as a number 1 to 12), and list your registration numbers in column F starting with F2. Then in G2 you can put this array* formula to get the maximum: =MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100)) and this one in H2 to get the minimum: =MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10)) I have assumed that you have up to 100 rows of data - adjust if you have more. *Array formulae need to be committed using the key combination of CTRL- SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. If you amend the formula you must use CSE again. However, you can use the normal copy/paste operation to copy G2:H2 down to cover the number of vehicles you have. Change the number in F1 to choose a different month. Hope this helps. Pete On May 5, 11:57 am, Robbie wrote: I am trying to get a min and a max value in column C for the various Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED 1 1/5/08 JWY467GP 10000 1000 2 5/5/08 PTS030GP 5000 500 3 6/5/08 MVT537GP 2000 250 4 7/5/08 JWY467GP 12000 1000 5 10/5/08 PTS030GP 6000 500 6 12/05/08 MVT537GP 2500 250 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum between date values
Good Day Jacob
To workout the min and max in the same date range in order for me to work out the km's done, would you agree with : MAX(IF((A$1:A$100)<=F$1)*(B$1:B$100=F2),C$1:C$100) ) and this one in H2 to get the minimum: =MIN(IF((A$1:A$100=F$1)*(B$1:B$100=F2),C$1:C$100, 10E10)) Thanking you for your time "Jacob Skaria" wrote: Try with your query dates in E1 and F1 E1 StartDate F1 End Date Change the reference to the truck number or reference to a acell (all in one line) =SUMPRODUCT(--(B2:B100="TruckReg"),--(A2:A100=E1),--(A2:A100<=F1),D2:D100) (Adjust the range as required) If this post helps click Yes --------------- Jacob Skaria "Robbie" wrote: Good Day I am currently using the below formula for min and max value by month(). I would like to go a step further now and recover the sum value between 2 date values. ie. diesel used between January to April per vehicle Appreciate the help "Robbie" wrote: Hi Pete Thank you for your help, the adding F1 Month will certainly help. Regards "Pete_UK" wrote: Use F1, say, to enter the month you are interested in (as a number 1 to 12), and list your registration numbers in column F starting with F2. Then in G2 you can put this array* formula to get the maximum: =MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100)) and this one in H2 to get the minimum: =MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10)) I have assumed that you have up to 100 rows of data - adjust if you have more. *Array formulae need to be committed using the key combination of CTRL- SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. If you amend the formula you must use CSE again. However, you can use the normal copy/paste operation to copy G2:H2 down to cover the number of vehicles you have. Change the number in F1 to choose a different month. Hope this helps. Pete On May 5, 11:57 am, Robbie wrote: I am trying to get a min and a max value in column C for the various Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED 1 1/5/08 JWY467GP 10000 1000 2 5/5/08 PTS030GP 5000 500 3 6/5/08 MVT537GP 2000 250 4 7/5/08 JWY467GP 12000 1000 5 10/5/08 PTS030GP 6000 500 6 12/05/08 MVT537GP 2500 250 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum between date values
Close...try the below (again Array entered)
=MAX(IF((A$1:A$100<=F1)*(B$1:B$100=F2),C$1:C$100)) Good day Robbie... If this post helps click Yes --------------- Jacob Skaria "Robbie" wrote: Good Day Jacob To workout the min and max in the same date range in order for me to work out the km's done, would you agree with : MAX(IF((A$1:A$100)<=F$1)*(B$1:B$100=F2),C$1:C$100) ) and this one in H2 to get the minimum: =MIN(IF((A$1:A$100=F$1)*(B$1:B$100=F2),C$1:C$100, 10E10)) Thanking you for your time "Jacob Skaria" wrote: Try with your query dates in E1 and F1 E1 StartDate F1 End Date Change the reference to the truck number or reference to a acell (all in one line) =SUMPRODUCT(--(B2:B100="TruckReg"),--(A2:A100=E1),--(A2:A100<=F1),D2:D100) (Adjust the range as required) If this post helps click Yes --------------- Jacob Skaria "Robbie" wrote: Good Day I am currently using the below formula for min and max value by month(). I would like to go a step further now and recover the sum value between 2 date values. ie. diesel used between January to April per vehicle Appreciate the help "Robbie" wrote: Hi Pete Thank you for your help, the adding F1 Month will certainly help. Regards "Pete_UK" wrote: Use F1, say, to enter the month you are interested in (as a number 1 to 12), and list your registration numbers in column F starting with F2. Then in G2 you can put this array* formula to get the maximum: =MAX(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100)) and this one in H2 to get the minimum: =MIN(IF((MONTH(A$1:A$100)=F$1)*(B$1:B$100=F2),C$1: C$100,10E10)) I have assumed that you have up to 100 rows of data - adjust if you have more. *Array formulae need to be committed using the key combination of CTRL- SHIFT-ENTER (CSE) rather than the usual ENTER. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - you should not type these yourself. If you amend the formula you must use CSE again. However, you can use the normal copy/paste operation to copy G2:H2 down to cover the number of vehicles you have. Change the number in F1 to choose a different month. Hope this helps. Pete On May 5, 11:57 am, Robbie wrote: I am trying to get a min and a max value in column C for the various Registrations in column B in a spreadsheet - Excel 2003. ie trying to get the first and last kilometer reading of the month per vehicle. Please can you help A-DATE B-TRUCK REG C-KILOMETERS D-DIESEL FILLED 1 1/5/08 JWY467GP 10000 1000 2 5/5/08 PTS030GP 5000 500 3 6/5/08 MVT537GP 2000 250 4 7/5/08 JWY467GP 12000 1000 5 10/5/08 PTS030GP 6000 500 6 12/05/08 MVT537GP 2500 250 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to sum up values in the date criteria | Excel Discussion (Misc queries) | |||
| How to pro rate values by date? | Excel Discussion (Misc queries) | |||
| Change Date Values | Excel Discussion (Misc queries) | |||
| How do I convert date values to non-date format e.g. 01-06-78? | Excel Discussion (Misc queries) | |||
| Date dependant values. | Excel Discussion (Misc queries) | |||
Hybrid Mode
