| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 78548 | 2007-04-19 23:10:00 | excell question | globe (11482) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 542436 | 2007-04-19 23:10:00 | hi i am trying to develop a spreadsheet that automatically adds values between dates that I input. I will have a list if dates (month and year) and next to these dates values and statistics. I would like to be able to get the 12 month cummulative value for the 12 month period that I specifiy What function do i need to use to make this happen ? I have tried to use the count if function and the sum if but it does not offer what I am after. thanks |
globe (11482) | ||
| 542437 | 2007-04-20 06:37:00 | Assuming your dates are in column A and values to sum are in Colum B, the following formula works: =SUMIF(A1:A23,"<="&DATE(2006,12,31),B1:B23)-SUMIF(A1:A23,"<"&DATE(2006,1,1),B1:B23) This gives the total of all items in Column B between 1/1/06 and 31/12/06. Hope that helps Lucy |
LucyL (2867) | ||
| 542438 | 2007-04-20 11:55:00 | Hi An alternative method to that posted by Lucy is to use the sumproduct function like this : =SUMPRODUCT(--(A1:A23>=C1),--(A1:A23<=C2),B1:B23) where A1:A23 contains the dates, B1:B23 contains the values you want to sum, C1 contains the start date and C2 contains the end date. Andrew |
andrew93 (249) | ||
| 1 | |||||