| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
counts of dates
I have a list of dates from 1/1/2000 to 1/1/2007 and i want to get the sum
of it monthly.i want to get the sum of 2/1/2006 to 2/28/2006. can you give me some tips? thanks! |
|
#2
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
|
counts of dates
Jjuan,
You could do it with a helper column. Let's say your dates are in column A. In another column, Let's say B, put =DATE(YEAR(A2),MONTH(A2),1). This will give the date as the first of the month, regardless of day of month. Format it something like mm-yy. You can hide it if you want to . Use that column in your grouping in the pivot table (drag it to the left or top). -- Earl Kiosterud www.smokeylake.com ----------------------------------------------------------------------- "jjuan" wrote in message ... I have a list of dates from 1/1/2000 to 1/1/2007 and i want to get the sum of it monthly.i want to get the sum of 2/1/2006 to 2/28/2006. can you give me some tips? thanks! |
|
#3
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
|
counts of dates
If you want to view all months, you should look at the pivot table
suggestion. If you only want one month at a time you could use a formula Assuming your dates are in A1:A5 and B1:B5 is the data you want totaled, D1 = 2/1/06 and E1=2/28/06, try: =SUMIF(A1:A5,"="&D1,B1:B5)-SUMIF(A1:A5,""&E1,Sheet2!B1:B5) "jjuan" wrote: I have a list of dates from 1/1/2000 to 1/1/2007 and i want to get the sum of it monthly.i want to get the sum of 2/1/2006 to 2/28/2006. can you give me some tips? thanks! |
|
#4
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
|
counts of dates
=SUMPRODUCT(--(TEXT(A2:A100,"m")="1"),B2:B100)
"jjuan" wrote: I have a list of dates from 1/1/2000 to 1/1/2007 and i want to get the sum of it monthly.i want to get the sum of 2/1/2006 to 2/28/2006. can you give me some tips? thanks! |
|
#5
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
|
counts of dates
to count
=sumproduct((year(a2:a22)=2006)*(month(a2:a22)=2)) to sum another column =sumproduct((year(a2:a22)=2006)*(month(a2:a22)=2)* c2:c22) -- Don Guillett SalesAid Software "jjuan" wrote in message ... I have a list of dates from 1/1/2000 to 1/1/2007 and i want to get the sum of it monthly.i want to get the sum of 2/1/2006 to 2/28/2006. can you give me some tips? thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pre-1900 dates | Excel Discussion (Misc queries) | |||
| Need to pull current dates from list w/many dates | Excel Discussion (Misc queries) | |||
| 2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
| How do I calculate if a date is in a certain time frame? | Excel Worksheet Functions | |||
| Formating Dates for production schedule | Excel Discussion (Misc queries) | |||
Hybrid Mode
