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

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

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

--
Rick (MVP - Excel)


"JRD" wrote in message
...
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

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:

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

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

--
Rick (MVP - Excel)


"JRD" wrote in message
...
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

.

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

--
Rick (MVP - Excel)


"JRD" wrote in message
...
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:

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

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

--
Rick (MVP - Excel)


"JRD" wrote in message
...
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

.

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:

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

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

--
Rick (MVP - Excel)


"JRD" wrote in message
...
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

.

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" wrote in message
...
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:

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

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

--
Rick (MVP - Excel)


"JRD" wrote in message
...
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

.

Try this...

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

--
Biff
Microsoft Excel MVP


"JRD" wrote in message
...
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:

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

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

--
Rick (MVP - Excel)


"JRD" wrote in message
...
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

.

=SUMPRODUCT((MONTH(A1:A50)=10)*(A1:A500))

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" wrote:

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