counting dates using SUMPRODUCT
 

I have a column with 100 rows that will eventually contain a publication
date. Those cells are formatted as dates. Some of the cells in the column
have dates and I need to count the number of publication dates in July,
August, etc. I'm using the formula

=SUMPRODUCT(--(MONTH(A1:A100)=7))

[7 being the number that represents July] and the formula yields the number
of publication dates in July.

My problem is that the formula is counting every blank cell as a January
date.
Any help you can give is greatly appreciated.

counting dates using SUMPRODUCT
 

On Mon, 27 Jul 2009 15:52:01 -0700, Marge
wrote:

I have a column with 100 rows that will eventually contain a publication
date. Those cells are formatted as dates. Some of the cells in the column
have dates and I need to count the number of publication dates in July,
August, etc. I'm using the formula

=SUMPRODUCT(--(MONTH(A1:A100)=7))

[7 being the number that represents July] and the formula yields the number
of publication dates in July.

My problem is that the formula is counting every blank cell as a January
date.
Any help you can give is greatly appreciated.

Try modifying your formula for January like this:

=SUMPRODUCT((MONTH(A1:A100)=1)*(A1:A100<""))

Hope this helps / Lars-Åke

counting dates using SUMPRODUCT
 

This way, using both month/year would always be unambiguous, eg:
=SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jan09"))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Marge" wrote:
I have a column with 100 rows that will eventually contain a publication
date. Those cells are formatted as dates. Some of the cells in the column
have dates and I need to count the number of publication dates in July,
August, etc. I'm using the formula

=SUMPRODUCT(--(MONTH(A1:A100)=7))

[7 being the number that represents July] and the formula yields the number
of publication dates in July.

My problem is that the formula is counting every blank cell as a January
date.
Any help you can give is greatly appreciated.

counting dates using SUMPRODUCT
 

Thank you! What I actually did was add a string to count the Januarys
greater than 2000 and it worked fine.

Thanks again for your help.

"Max" wrote:

This way, using both month/year would always be unambiguous, eg:
=SUMPRODUCT(--(TEXT(A1:A100,"mmmyy")="Jan09"))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Marge" wrote:
I have a column with 100 rows that will eventually contain a publication
date. Those cells are formatted as dates. Some of the cells in the column
have dates and I need to count the number of publication dates in July,
August, etc. I'm using the formula

=SUMPRODUCT(--(MONTH(A1:A100)=7))

[7 being the number that represents July] and the formula yields the number
of publication dates in July.

My problem is that the formula is counting every blank cell as a January
date.
Any help you can give is greatly appreciated.

counting dates using SUMPRODUCT
 

Welcome, glad you got going there
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Marge" wrote in message
...
Thank you! What I actually did was add a string to count the Januarys
greater than 2000 and it worked fine.

Thanks again for your help.