Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table in excel with three columns:

Name  
Start Date  
End Date  

I want to create a chart with count on the left hand side and date on the bottom to show the count of open item (defines as > start date and < end date) over time. So for example, if my data looked like this

Name  Start   End  
===   ====    ====  
Dog   2010    2012  
Cat   2011    2012  
Mouse 2000    2008  

That it would show a graph that would show 1 at year 2000 (as that was the first start date) and then go back to 0 in 2008 (as that ended) and then go to 1 in 2010 and then to 2 in 2011 and then to 0 in 2012.

share|improve this question

You have to set an intermediate table where you put your years and a countifs formula. Here is what it would be assuming your data is in Sheet1

       A      B
1    Year   Count
2    2000   =countifs(Sheet1!$B$2:B$4,">=" &A2,Sheet1!$C$2:C$4,"<=" &A2)
3    2001   copy down formula from above
4    etc.

Once that is set, graphing it will be easy.

share|improve this answer

You must log in to answer this question.

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