Counting cells with specific month in

JRD

unread,

Nov 22, 2009, 4:18:02 PM11/22/09

to

How can I count the number of cells in date format which contain a certain
month

Example:
A
1 01/10/2009
2 01/09/2009
3 20/09/2009
4 12/10/2009

How can I count the number of cells in column A which contain an October
date. The answer here would be 2.

Thanks

Rick Rothstein

unread,

Nov 22, 2009, 4:42:41 PM11/22/09

to

Try this formula (set the month number, 10 in this case, as needed)...

=SUMPRODUCT(--(MONTH(A1:A4)=10))

--
Rick (MVP - Excel)

"JRD" <J...@discussions.microsoft.com> wrote in message
news:ED76B82C-3517-4C1E...@microsoft.com...

Gary''s Student

unread,

Nov 22, 2009, 5:14:02 PM11/22/09

to

=SUMPRODUCT((MONTH(A1:A50)=10)*(A1:A50>0))

So the following data returns 7:

January 22, 2009
July 7, 2009
October 19, 2009
April 11, 2009
May 31, 2009
October 30, 2009
August 9, 2009
March 6, 2009
December 12, 2009
August 21, 2009
February 13, 2009
June 5, 2009
September 7, 2009
April 11, 2009
September 2, 2009
January 24, 2009
June 26, 2009
November 27, 2009
October 9, 2009
August 23, 2009
June 3, 2009
March 29, 2009
March 28, 2009
March 18, 2009
April 22, 2009
March 2, 2009
January 13, 2009
July 13, 2009
July 7, 2009
March 15, 2009
October 30, 2009
July 25, 2009
December 17, 2009
January 25, 2009
January 18, 2009
March 1, 2009
December 25, 2009
December 22, 2009
November 1, 2009
October 15, 2009
January 13, 2009
November 1, 2009
June 14, 2009
December 8, 2009
June 3, 2009
August 7, 2009
October 29, 2009
July 8, 2009
October 4, 2009
January 8, 2009

--
Gary''s Student - gsnu200909

JRD

unread,

Nov 22, 2009, 5:28:01 PM11/22/09

to

Can I use this formula to look down a whole column where the first row is not
actually a date (it is a heading)?

Thanks

"Rick Rothstein" wrote:

> .
>

JRD

unread,

Nov 22, 2009, 5:34:01 PM11/22/09

to

What I actually need to do is the following:

Example:
> > A B
> > 1 01/10/2009 Cancelled
> > 2 01/09/2009 Reported
> > 3 20/09/2009 Cancelled
> > 4 12/10/2009 Reported

How do I count the number of cells in column B that contain "reported" from
the month of october in column A - in this example the answer is 1.

Thanks

"Rick Rothstein" wrote:

> .
>

Rick Rothstein

unread,

Nov 22, 2009, 7:27:23 PM11/22/09

to

Just make the starting cell in the range A2 instead of A1.

--
Rick (MVP - Excel)

"JRD" <J...@discussions.microsoft.com> wrote in message

news:7549D7C1-33DC-469D...@microsoft.com...

Rick Rothstein

unread,

Nov 22, 2009, 7:31:22 PM11/22/09

to

For future reference, you should always ask the question you want answered,
not a simplification of it... Excel solutions tend to be targeted to the
described setup and what you want from it. For your "new" question...

=SUMPRODUCT((MONTH(A1:A4)=10)*(B1:B4="Reported"))

--
Rick (MVP - Excel)

"JRD" <J...@discussions.microsoft.com> wrote in message

news:1E9BB493-6BDD-4480...@microsoft.com...

T. Valko

unread,

Nov 22, 2009, 7:53:53 PM11/22/09

to

Try this...

=SUMPRODUCT(--(MONTH(A2:A5)=10),--(B2:B5="reported"))

--
Biff
Microsoft Excel MVP

"JRD" <J...@discussions.microsoft.com> wrote in message

news:1E9BB493-6BDD-4480...@microsoft.com...