SOLVED

Excel formula: Identifying no.of task happening in a different month

Occasional Contributor

Hi all,
I am working on a project where you have Tasks separated into workstreams and have a start + end date.
This allows be to get a gant and all, but I need to be able to show by workstream leaders,
- the no. of tasks thats on-going in a particular month,
- No. of tasks starting in a particular month and
- no. of tasks ending in a particular month

So that they can estimate workload, target turnover rates.

Task 

WorkstreamStart monthEnd Month
Task1PMOJul-21Jul-22
Task2ITFeb-21Jun-21
Task3ProcurementMay-21May-22
Task4Ops

Jul-21

Nov-21
Task5ITMay-21Feb-22
Task6OpsJun-21Aug-22
Task7ProcurementMay-21Oct-21
Task8SalesJun-21Dec-21

 

Monthtotal on-going taskNew Task starting this month Task ending this month
Jan-21   
Feb-21   
Mar-21   
Apr-21   
all the way to Aug 22   

this needs to be filterable by workstreams.
Getting no. of tasks Starting and ending is achieveable, as it a direct pivot using start + end date.
I am unable to identify ways to count for, in example task 2, count for Feb-21, Mar-21, Apr-21, May-21 & Jun-21, such that it appears under total on-going tasks for those months.

Thanks in advance for the help, I've been losing my mind on this.
Desired outcome for Lets Say IT workstream will be 

Monthtotal on-going taskNew Task starting this month Task ending this month
Jan-21   
Feb-2111 
Mar-211  
Apr-211  
May-212 1
Jun-2121 
Jul-211  
Aug-211  
Sep-211  
Oct-211  
Nov-211  
Dec-211  
Jan-221  
Feb-221 1
4 Replies
So, starting and ending months also will be counted as ongoing month? Can you put your desired output to 2nd table manually so that we can try to achieve that by formula.
I've added the desired outcome on the initial post.
best response confirmed by Pratichi (Occasional Contributor)
Solution

@Pratichi 

Try COUNTIFS() function with date criteria like- (download the attach file).

 

=COUNTIFS($C$2:$C$9,"<="&F3,$D$2:$D$9,">="&F3,$B$2:$B$9,"IT")

 

Harun24HR_0-1659673827505.png