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!

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!

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!

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!

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!