Formula to assist with counting dates

D

Dilly

I am using the formula below to count the number of dates in a certain month
and year from a random list of dates. However my "list" of dates is not a
continuous string of dates and is split by some text and some headings. When
i use this formula it highlights a value error. Is their any way around this
??

=SUMPRODUCT(--(MONTH(A1:A100)=2),--(YEAR(A1:A100)=2009))
 
S

Sheeloo

Try
=SUMPRODUCT(--(MONTH(IF(ISNUMBER(A1:A100),A1:A100,0))=2),--(YEAR(IF(ISNUMBER(A1:A100),A1:A100,0))=2009))

This is an Array formula, so press CTRL-ENTER-SHIFT after typing/pasting.
 
H

Harlan Grove

Sheeloo said:
Try

=SUMPRODUCT(--(MONTH(IF(ISNUMBER(A1:A100),A1:A100,0))=2),
--(YEAR(IF(ISNUMBER(A1:A100),A1:A100,0))=2009))

This is an Array formula, so press CTRL-ENTER-SHIFT after typing/pasting.
....

Yes, it is an array formula, so why not use

=SUM(IF(ISNUMBER(A1:A100),(MONTH(A1:A100)=2)*(YEAR(A1:A100)=2009)))

or avoid array formulas and use

=SUMPRODUCT(--(TEXT(A1:A100,"yyyy\-mm;;;")="2009-02"))
 

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