Formula to count Month in Dates

K

K

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
 
B

Bob Phillips

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

JE McGimpsey

One way:

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

Copy down as needed.
 
M

Mike H

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

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

Mike
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top