counts of dates

J

jjuan

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!
 
E

Earl Kiosterud

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).
 
J

JMB

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)
 
D

Don Guillett

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)
 
Top