counting dates using SUMPRODUCT

M

Marge

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.
 
L

Lars-Åke Aspelin

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.
Click to expand...

Try modifying your formula for January like this:

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

Hope this helps / Lars-Åke
 
M

Marge

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 said:
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 said:
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.
Click to expand...
Click to expand...
 

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

Similar Threads

counting dates (months) using SUMPRODUCT 1
Excel Sumproduct 0
Count total balance using two numbers 5
Formula to assist with counting dates 2
Sumproduct, countif and format? 0
Excel Help with dates 2
count total dates for each month 2
SUMPRODUCT help with dates 4

Top