count dates

[rabol]

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

[Roger Govier]

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.

--
Regards
Roger Govier
"rabol" <rabol@discussions.microsoft.com> wrote in message
news:55B72B1C-FDB0-4EEB-A7DA-737EC1E9BEF4@microsoft.com...
> 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
>
>

[Ragdyer]

Try this:

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

And copy down, where each row will be the following month.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"rabol" <rabol@discussions.microsoft.com> wrote in message
news:55B72B1C-FDB0-4EEB-A7DA-737EC1E9BEF4@microsoft.com...
> 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
>
>

[Ragdyer]

Sorry, since I suggested copying down, you'll need the absolute references:

=SUMPRODUCT(--(MONTH($C$1:$C$25)=ROW(A1)))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"rabol" <rabol@discussions.microsoft.com> wrote in message
news:55B72B1C-FDB0-4EEB-A7DA-737EC1E9BEF4@microsoft.com...
> 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
>
>