ExcelFilter: Between Day 1 and Day 90, I had how many of which?

September 2, 2009 7:09 AM

How can I count certain things in a range of cells in Excel that fall between two dates?

I've got an Excel workbook that keeps track of every thing I produce at my job. (What the things are is irrelevant.) The date it's created in in Column A. In Column F there are criteria to determine which type of thing is is (two specific ones). I would like to count the number of each that fall between two dates, i.e. between, say 9/01/2009 and 11/30/2009.

For the life of me, I can't figger it out.

I've tried wrapping my head around dynamic ranges, but all I can see for doing that is OFFSET(), which would be great if all the date ranges had the same amount of rows, but they don't. Besides, I add new rows to this thing regularly, as each new thingie is generated. So, having, say, the last 30 of these counted does not help.

Google searching has given me info about how to count for the last x of a certain range, where x is days, weeks, months, etc. Doesn't help.

Oh, and I *really* would not like to have to go into VBA scripting for this.

So, uh, help?

posted by SansPoint to computers & internet (5 comments total)

Your question is a bit unclear. Are you looking to count the number of days between two dates? If so, the undocumented DATEDIF function will help. See here for more: http://www.cpearson.com/excel/datedif.aspx
posted by dfriedman at 7:24 AM on September 2

Not trying to count the number of days, but the number of another item, in certain range of dates. The number of "X" in a range between, say, 01/01/09 and 03/31/09
posted by SansPoint at 7:31 AM on September 2

OK, use COUNTIF: http://www.techonthenet.com/excel/formulas/countif.php

The date in question should be a text string withing the formula; that is, it needs to be enclosed by double quotes: "
posted by dfriedman at 7:32 AM on September 2

I think that you need to use an array formula with a SUMPRODUCT with your date range in it plus your other qualifier. I've used this one before:

=SUMPRODUCT(--(Data!B2:B1200>=DATEVALUE("1/1/05")),--(Data!B2:B1200<=DATEVALUE("31/12/05"))*(Data!U2:U1200="yes"))

Where your date column and range is my B2:B1200 and your other column is my U2:U1200="X").

When entering that, hit Shift+Enter for the formula to work (this makes it an array).
posted by urbanlenny at 7:35 AM on September 2 [1 favorite has favorites]

urbanlenny That got it! Thanks!
posted by SansPoint at 7:41 AM on September 2