count dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

How do I count then rows where the date is within a certin month ?

eg:
A B C
1 xxx some text 01-01-2005
2 yyy some text 31-01-2005
3 zzz some text 01-02-2005
4 vvv some text 01-03-2005

6 Number of rows where month is 01 (2)
7 Number of rows where month is 02 (1)
8 Number of rows where month is 03 (1)

Thanks in advance.
Best
Steen
 
One way would be
=SUMPRODUCT(--(MONTH(C1:C4)=1)) for January

You could put month numbers 1 - 12 in cells D1:D12 then enter in E1
=SUMPRODUCT(--(MONTH($C$1:$C$4)=D1))
then copy down through E2:E12

Change your range of C1:C4 to suit the larger range of data you will
undoubtedly have.
 
Try this:

=SUMPRODUCT(--(MONTH(C1:C25)=ROW(A1)))

And copy down, where each row will be the following month.
 
Sorry, since I suggested copying down, you'll need the absolute references:

=SUMPRODUCT(--(MONTH($C$1:$C$25)=ROW(A1)))
 
Back
Top