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.
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.
In cell AJ2, we've created the following array formula:
=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.
