Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sum values between dates
In col C8:C200 I have start dates entered by date and time. In col J8:J200
I have end dates entered by date and time. In col N8:N200 I have values for each transcation. What I would like to do is in cell C1 enter a start date. In cell C2 enter a end date. In cell C3 have a formula that based on C1 and C2 find the dates that are equal to, or between the start and end dates and based on those match sum the values in col N |
#3
|
|||
|
|||
Don
Thanks for the quick reply. However the formula doesn't seem to work. It returns a value but it is not correct. I am sure it must have something to do with the way the dates are recorded. When I create a simple sheet and enter dates, your formula works fine. So that is why I know it must be the date/time. All the data is downloaded so I don't enter any of the data directly. Here is what the date/time info looks like in a cell in Col C: 6/6/2005 12:23:00 AM I know the format is ok, because I can go into format and change the date display. Any further ideas? Frick "Don Guillett" wrote in message ... try =sumproduct((c8:c200c1)*(j8:j200<c2)*n8:n200) -- Don Guillett SalesAid Software "Scott Frickman" wrote in message ... In col C8:C200 I have start dates entered by date and time. In col J8:J200 I have end dates entered by date and time. In col N8:N200 I have values for each transcation. What I would like to do is in cell C1 enter a start date. In cell C2 enter a end date. In cell C3 have a formula that based on C1 and C2 find the dates that are equal to, or between the start and end dates and based on those match sum the values in col N |
#4
|
|||
|
|||
=SUMPRODUCT((INT($C$8:$C$100)=C1)+0,(INT($C$8:$C$ 100)<=C2)+0,$J$8:$J$200)
Scott Frickman wrote: In col C8:C200 I have start dates entered by date and time. In col J8:J200 I have end dates entered by date and time. In col N8:N200 I have values for each transcation. What I would like to do is in cell C1 enter a start date. In cell C2 enter a end date. In cell C3 have a formula that based on C1 and C2 find the dates that are equal to, or between the start and end dates and based on those match sum the values in col N -- [1] The SumProduct function should implicitly coerce the truth values to their Excel numeric equivalents. [2] The lookup functions should have an optional argument for the return value, defaulting to #N/A in its absence. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using dates for x-axis values as string instead of creating a scale | Charts and Charting in Excel | |||
How to dates column with values in next column | Excel Discussion (Misc queries) | |||
FORMULA REQD FOR ADD ING DATES AND VALUES AND PART NUMBERS | Excel Discussion (Misc queries) | |||
SUM values between dates | Excel Worksheet Functions | |||
Calculating for number of days when values are in dates | Excel Worksheet Functions |