Sign up ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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)


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.

share|improve this question
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 Aug 15 '12 at 0:49
@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 Aug 15 '12 at 15:26
Thanks @Excellll, that's a good trick I'm sure to need in the future. –  Mike Fitzpatrick Aug 15 '12 at 23:57
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 to Deploy!T3:T60. –  Excellll Aug 16 '12 at 13:49
@ 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 Aug 16 '12 at 18:04

2 Answers 2

I tried your formula in Excel 2003 and it works for me - SUMPRODUCT and TODAY are built-in functions in Excel 2003 but EDATE is part of Analysis ToolPak add-in - if you don't have that add-in installed then you will probably get a #VALUE! error.

Try enabling Analysis ToolPak

Tools > add-ins > tick "Analysis ToolPak" box. You may need to re-enter the formula.

If that doesn't work or if you don't want to or can't install add-ins then you can get the date from 12 months back with this formula


....or you can just use TODAY()-365 (although that's obviously less accurate because it doesn't take leap years into account)

so that would be either


or just


If you still get errors then perhaps you have errors in the data range Deploy!T3:T60. If so then an alternative approach is to subtract one COUNTIF function from another. That helps because COUNTIF will ignore errors in the data, formula would be


share|improve this answer

Assuming your dates are formatted as Dates (not dates + times) and you don't mind having an extra column, you could try this:

enter image description here

  • Cell B2 contains today's date, =TODAY().
  • Cells B5-B11 contain the dates you want to count.
  • Cells C5-C11 contain the difference between today's date and the dates in your list, calculated as the subtraction B5-$B$2.
  • Cell C13 contains the formula `=COUNTIF(C5:C13,"<365")
share|improve this answer
Thanks for your response but it is not the solution that I am looking for. The result im getting is an error message if i remember it was #REF. The double negative was something i seen in a forum post and i was unsure of what it done. so i left it in and the formula worked so i seen no reason to remove it. –  J0hn0 Aug 15 '12 at 14:30
@J0hn0 what is wrong with it? –  soandos Aug 15 '12 at 15:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.