Hi, i have dates in one column cells (please see below)

10/06/2005
24/06/2005
03/08/2005
23/08/2005
18/09/2005
21/09/2005
28/09/2005
30/09/2005
05/10/2005
08/10/2005
18/10/2005
19/10/2005
02/11/2005
22/11/2005
26/11/2005
06/12/2005
16/12/2005
21/12/2005

i need a formula in next cell of each date to count the dates which
have same month like in top two dates i have month 6 so formula should
go throw all dates and see which dates are have month 6 and count them
and count result should come to 2 as we got only two dates which are
in month 6. and fourmula should also do same with other dates. i am
trying to count the total of all those dates which have same months.
Please if anybody can help. i dont need macro

=SUMPRODUCT(--(MONTH(A1:A20)=1))

if any can be blank use

=SUMPRODUCT(--(MONTH(A1:A20)=1),--)A1:A20<""))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"K" wrote in message
...
Hi, i have dates in one column cells (please see below)

10/06/2005
24/06/2005
03/08/2005
23/08/2005
18/09/2005
21/09/2005
28/09/2005
30/09/2005
05/10/2005
08/10/2005
18/10/2005
19/10/2005
02/11/2005
22/11/2005
26/11/2005
06/12/2005
16/12/2005
21/12/2005

i need a formula in next cell of each date to count the dates which
have same month like in top two dates i have month 6 so formula should
go throw all dates and see which dates are have month 6 and count them
and count result should come to 2 as we got only two dates which are
in month 6. and fourmula should also do same with other dates. i am
trying to count the total of all those dates which have same months.
Please if anybody can help. i dont need macro

One way:

B1: =SUMPRODUCT(--(MONTH($A$1:$A$18)=MONTH(A1)))

Copy down as needed.

In article
,
K wrote:

Hi, i have dates in one column cells (please see below)

10/06/2005
24/06/2005
03/08/2005
23/08/2005
18/09/2005
21/09/2005
28/09/2005
30/09/2005
05/10/2005
08/10/2005
18/10/2005
19/10/2005
02/11/2005
22/11/2005
26/11/2005
06/12/2005
16/12/2005
21/12/2005

i need a formula in next cell of each date to count the dates which
have same month like in top two dates i have month 6 so formula should
go throw all dates and see which dates are have month 6 and count them
and count result should come to 2 as we got only two dates which are
in month 6. and fourmula should also do same with other dates. i am
trying to count the total of all those dates which have same months.
Please if anybody can help. i dont need macro

On 2 Jan, 17:03, JE McGimpsey wrote:
One way:

B1: * * *=SUMPRODUCT(--(MONTH($A$1:$A$18)=MONTH(A1)))

Copy down as needed.

In article
,



*K wrote:
Hi, i have dates in one column cells (please see below)

10/06/2005
24/06/2005
03/08/2005
23/08/2005
18/09/2005
21/09/2005
28/09/2005
30/09/2005
05/10/2005
08/10/2005
18/10/2005
19/10/2005
02/11/2005
22/11/2005
26/11/2005
06/12/2005
16/12/2005
21/12/2005

i need a formula in next cell of each date to count the dates which
have same month like in top two dates i have month 6 so formula should
go throw all dates and see which dates are have month 6 and count them
and count result should come to 2 as we got only two dates which are
in month 6. *and fourmula should also do same with other dates. *i am
trying to count the total of all those dates which have same months.
Please if anybody can help. *i dont need macro- Hide quoted text -

- Show quoted text -

Thanks everyone specially JE Mcgimpsey

Put this in a cell and drag down 12 rows for Jan to December

=SUMPRODUCT(--(MONTH($A$1:$A$20)=ROW(A1)))

Mike

"K" wrote:

Hi, i have dates in one column cells (please see below)

10/06/2005
24/06/2005
03/08/2005
23/08/2005
18/09/2005
21/09/2005
28/09/2005
30/09/2005
05/10/2005
08/10/2005
18/10/2005
19/10/2005
02/11/2005
22/11/2005
26/11/2005
06/12/2005
16/12/2005
21/12/2005

i need a formula in next cell of each date to count the dates which
have same month like in top two dates i have month 6 so formula should
go throw all dates and see which dates are have month 6 and count them
and count result should come to 2 as we got only two dates which are
in month 6. and fourmula should also do same with other dates. i am
trying to count the total of all those dates which have same months.
Please if anybody can help. i dont need macro