totn Excel

MS Excel 2007: Use an array formula to count the number of dates exceeding 12 months

This Excel tutorial explains how to use an array formula to count the number of dates exceeding 12 months in Excel 2007 (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2007, I have cells that are conditionally formatted so that they turn red depending on the return from a formula. I need another cell to count the column to find the total number that are red. Red cells are the ones exceeding 12 months, based on the following formula:

=(YEAR($B$2)-YEAR(AD19:AF32))*12+MONTH($B$2)-MONTH(AD19:AF32)>=12

How can I count the number of red cells?

Answer: This solution does not directly count the red cells, but rather uses the same calculation in an array formula to count the cells for you.

Let's look at an example.

Microsoft Excel

In cell B2, we have a control date that we want to compare to all of the dates in the range AD19:AF32. We want to count the number of dates in range AD19:AF32 that are 12 months or older than the date in B2. We will put the result of this calculation in cell AJ2.

Microsoft Excel

In cell AJ2, we've created the following array formula that uses the SUM, YEAR, MONTH and ISBLANK functions:

=SUM(((YEAR($B$2)-YEAR(AD19:AF32))*12+MONTH($B$2)-MONTH(AD19:AF32)>=12)*(ISBLANK(AD19:AF32)=FALSE))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=SUM(((YEAR($B$2)-YEAR(AD19:AF32))*12+MONTH($B$2)-MONTH(AD19:AF32)>=12)*(ISBLANK(AD19:AF32)=FALSE))}

The ISBLANK function must be included in this array formula so that empty cells are not included in the calculation.

As you can see, the array formula in cell AJ2 has returned 4, which represent the 4 red dates that are at least 12 months older than the control date in cell B2.

Share on: