Count the cell If the date falls within the month of a specific year I have 3500

Question

Count the cell If the date falls within the month of a specific year

I have 3500+ rows of data. Column AI contains an expiration date. I am trying to calculate the number of entries that expire within a given month and year. I am using the following formula:

=SUMPRODUCT(--(YEAR(AI5:AI10)=2014),--(MONTH(AI5:AI10)=6))

Within the range AI5:AI10 I know there is only a single cell that falls within the range for 6/2014 and this formula works fine. I also tested by manipulating some of the dates within this range and it auto calculated correctly.

When I expand the range to include the additional 3500+ rows it results in a #VALUE error.

The ranges below all result in the above error:

AI5:AI100

AI:AI

AI5:AI1000

etc.

What I discovered is that the error is generated because some of the cells contain a non date value. For example "Archive 2014" for records no longer being tracked. How can I use the formula above to not error if one of the cells in the range doesn't contain a numeric / date value???

Thanks for help !!!

Explanation / Answer

Hi.....

Try this one.

=SUMPRODUCT(--(TEXT(AI5:AI5000,"MMYYYY")="062014"))

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects