0
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.
Firstly, are you getting the wrong result or an error such as #REF or #NA? Secondly, are the double negatives (--) in your formula intentional or a typo? – Mike Fitzpatrick – 2012-08-15T00:49:05.360
1@MikeFitzpatrick The double negative is a trick for converting Boolean values to numeric values in Excel. I believe
SUMPRODUCT()
will return an error if it is passed an array of Boolean values, so it's necessary to make the conversion. – Excellll – 2012-08-15T15:26:26.823Thanks @Excellll, that's a good trick I'm sure to need in the future. – Mike Fitzpatrick – 2012-08-15T23:57:40.373
I don't have a copy of Excel 2003 to work with, but (just a hunch) have you tried taking the extra "Deploy!" instances out of each range reference? I.e., change
Deploy!T3:Deploy!T60
toDeploy!T3:T60
. – Excellll – 2012-08-16T13:49:57.657@ Excelll
Deploy!T3:Deploy!T60
should work OK, although I would always use your alternative. I think the problem is with EDATE, see my answer – barry houdini – 2012-08-16T18:04:39.543