I am by no means overly proficient with Excel, I know enough to do the basics.
After much forum and soul searching I had come up with the following formula, which I will discuss what it is supposed to do. (It seems to work fine in Office 2010 but doesnt work at all in 2003 which unbeknownst to me is what the file must be formatted to)
=SUMPRODUCT(--(Deploy!T3:Deploy!T60<=TODAY()),--(Deploy!T3:Deploy!T60>(EDATE(TODAY(),-12))))
Deploy
being the Sheet name, cells T3-T60
are the calculation cells.
The formula is to work out the number of cells that fall in a date range 1 year prior to today's date.
If someone can figure out a simplified version of the formula, it would be much appreciated, since I use this formula and a few modified versions of it (mostly month count changes) throughout the worksheet.
SUMPRODUCT()
will return an error if it is passed an array of Boolean values, so it's necessary to make the conversion.Deploy!T3:Deploy!T60
toDeploy!T3:T60
.Deploy!T3:Deploy!T60
should work OK, although I would always use your alternative. I think the problem is with EDATE, see my answer