• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hot to Count Records Between 2 Dates And a Condition?

L

Lou

New Member
First post, looking for help w/Excel 2007. I have a data set with one column having dates extending to two years. Second column next to data column specifies a status, i.e. "Closed" or "Active" or "Canceled". I have specific starting and ending dates where if a record is listed and it is "Closed", I want to count the totals which comply, looking at the two columns.


Example:


Date,, Status,, End, Date,, 2/10/2010

2/1/2009,, Closed

1/15/2008,, Closed,, Start, Date,, 6/10/2009

10/1/2009,, Closed

9/20/2009,, Closed

1/10/2010,, Active

12/20/2009,, Active,, Records, Closed:,, ?????

5/2/2009,, Closed

9/20/2008,, Closed

8/1/2008,, Canceled

8/20/2009,, Closed


I need to count the records between the starting and end dates, that have 'Closed', only.


I can generate the total records between the two specified dates but not if I stipulate "Closed". Conversely, I can total up ALL the closed records, including those that exceed the start and end dates.


Any help would be greatly appreciated, from an Excel newbie.


Thanks.


Lou
 
Lou

I assume that:

Date is in Column A

Status is in Column C

End Date is in E1

Start date is in E3


then in E7 put


=+SUMPRODUCT(1*(A2:A11>=E3)*(A2:A11<=E1)*(C2:C11="Closed"))
 
Hui:


Hi and thank you. I will try this and get back to you with the results. The actual columns are much as you assumed. This is an excellent suggestion, I think, and where the columns vary, I think I can adapt your formula. This provides the 'bones' I need for my data to be properly crunched. I tried some variations of this, but nothing this clean or neatly put strung. I'm keeping fingers crossed. I'll let you known.


Thank You !


Lou
 
Hui:


THANK YOU !! It appears your formula works like a champ. The actual columns for my spreadsheet data were slightly different from your assumptions, but that was my fault as my post was after a long day, and I was trying to convey the basic structure of what I needed and was grappling with. As I said, your advice provided the "Good Bones" and I was able to adapt and adjust for my data. I tested the formula by changing dates and the text in the applicable columns, and your formula kept track and did the trick right on!!


Kudos. Greatly appreciated.


Lou
 
You must log in or register to reply here.
Back
Top