Hi all
In Column D I have a date received of a new job
In Column J I have "Team A" or "Team B", "Team C", "Team D", Team E"
In Column K I have status (New In, In Progress, Raised, Rejected, Withdrawn)
In Column L I have a date of that status
So,
What I need a formula is to count the number of jobs that are either "New In" or in "WIP" in a month.
So, in March all 5 jobs above (at some point or another) would have been in "New In" / "WIP" status
With Job 4 that was raised on 1st April, this would (for reporting purposes) only be "WIP/New In" in March
With Job 5 that was RAISED on 2 April 2021, this needs to have a count in both March and April
So
To simplify this example, I have just used Team A in the table, but it will be either Team A, or not Team A (as Team B, C, D, and E will all be clumped together for reporting)
In Column D I have a date received of a new job
In Column J I have "Team A" or "Team B", "Team C", "Team D", Team E"
In Column K I have status (New In, In Progress, Raised, Rejected, Withdrawn)
In Column L I have a date of that status
So,
| Column D (Date Received) | Column J (Team A / Team B) | Column K (Latest Status) | Column L (Date of Last Status Update) | |
| 01/03/21 | Team A | NEW IN | 01/03/21 | Job 1 |
| 03/03/21 | Team A | WIP | 05/03/21 | Job 2 |
| 31/03/21 | Team A | RAISED | 31/03/21 | Job 3 |
| 31/03/21 | Team A | RAISED | 01/04/21 | Job 4 |
| 31/03/21 | Team A | RAISED | 02/04/21 | Job 5 |
What I need a formula is to count the number of jobs that are either "New In" or in "WIP" in a month.
So, in March all 5 jobs above (at some point or another) would have been in "New In" / "WIP" status
With Job 4 that was raised on 1st April, this would (for reporting purposes) only be "WIP/New In" in March
With Job 5 that was RAISED on 2 April 2021, this needs to have a count in both March and April
So
| New In / WIP | ||
| March 2021 | 5 | (all jobs) |
| April 2021 | 1 | (only Job 5 to be counted) |
To simplify this example, I have just used Team A in the table, but it will be either Team A, or not Team A (as Team B, C, D, and E will all be clumped together for reporting)
