counts of dates

  • Thread starter Thread starter jjuan
  • Start date Start date
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!
 
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).
 
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)
 
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)
 
Back
Top