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.

5
  • 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? Aug 15, 2012 at 0:49
  • 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
    Aug 15, 2012 at 15:26
  • Thanks @Excellll, that's a good trick I'm sure to need in the future. Aug 15, 2012 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, 2012 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 Aug 16, 2012 at 18:04

2 Answers 2

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

=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))

....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

=SUMPRODUCT(--(Deploy!T3:T60<=TODAY()),--(Deploy!T3:T60>DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))

or just

=SUMPRODUCT(--(Deploy!T3:T60<=TODAY()),--(Deploy!T3:T60>TODAY()-365))

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

=COUNTIF(Deploy!T3:T60,"<="&TODAY())-COUNTIF(Deploy!T3:T60,"<="&DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())))

0

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")
2
  • 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, 2012 at 14:30
  • @J0hn0 what is wrong with it?
    – soandos
    Aug 15, 2012 at 15:42

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .