Forum Discussion
Count Open Jobs on any particular date
Hello,
I have a large spreadsheet of logged jobs. Each has an open date and a closed date. What I'd like to do is show how many jobs were open on any given date - and then ideally create a graph showing the number of open jobs changing over time. Is this possible?
Thanks
You could use
=COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,">="&E2)+COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,"")
6 Replies
Let's say the Open Dates are in B2:B1000 and the Closed Dates in C2:C1000.
Elsewhere, enter the start date for the chart, then fill down to the end date.
Let's say this date series is in E2:E32.
In F2, enter the formula
=COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,">="&E2)
and fill down.
Use columns E and F for your chart.
See the attached sample workbook.
Thanks so much for sending this - it work brilliantly. However I don't think it has an allowance for jobs that are still open. i.e. my data set has over 1000 open jobs which don't have an a closed date - however these are recorded as being open. I assume that this is because they need to have a closed date to be counted. Is there anyway to include these still open jobs?
thanks
You could use
=COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,">="&E2)+COUNTIFS($B$2:$B$1000,"<="&E2,$C$2:$C$1000,"")
