I have to list job numbers on a spreadsheet showing daily projects currently in progress. On another tab, I count in progress jobs and complete jobs. My problem is that job numbers may appear twice or more on a worksheet. For example, these job are in progress for today:
- 405a
- 406a
- 407a
- 408a
I use COUNTA to count all the jobs and total them on a dashboard so I have 4 jobs in progress today. If a job is still in progress tomorrow, it will appear again in the same worksheet, on another row, with "IP" in the cell next to it. Once the job is complete, there will be a date that appears in the "Date Completed" column. Let us assume that tomorrow, all jobs except 405a are complete. The worksheet now looks like this:
https://app.box.com/s/dfxsgar2yda9hjx83sqs007f8ed1nok8
Since only 1 unit is in progress and 3 are complete, I want the count on my dashboard to be 3 complete and 1 in progress. If I use COUNTA, it will count 8 units so that will not work. If I do COUNTIF on the "IP" jobs, that will not work because it will count 5 total "IP" jobs. I need formula to ignore the previous in progress statuses on that job. How do I write a formula that, in this scenario, gives me a total of 3 completed jobs and 1 in progress?
- 405a
- 406a
- 407a
- 408a
I use COUNTA to count all the jobs and total them on a dashboard so I have 4 jobs in progress today. If a job is still in progress tomorrow, it will appear again in the same worksheet, on another row, with "IP" in the cell next to it. Once the job is complete, there will be a date that appears in the "Date Completed" column. Let us assume that tomorrow, all jobs except 405a are complete. The worksheet now looks like this:
https://app.box.com/s/dfxsgar2yda9hjx83sqs007f8ed1nok8
Since only 1 unit is in progress and 3 are complete, I want the count on my dashboard to be 3 complete and 1 in progress. If I use COUNTA, it will count 8 units so that will not work. If I do COUNTIF on the "IP" jobs, that will not work because it will count 5 total "IP" jobs. I need formula to ignore the previous in progress statuses on that job. How do I write a formula that, in this scenario, gives me a total of 3 completed jobs and 1 in progress?
