Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

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

Your Answer

 
discard

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.