SOLVED

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

Occasional Contributor

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

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.

 Month total on-going task New 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

 Month total on-going task New Task starting this month Task ending this month Jan-21 Feb-21 1 1 Mar-21 1 Apr-21 1 May-21 2 1 Jun-21 2 1 Jul-21 1 Aug-21 1 Sep-21 1 Oct-21 1 Nov-21 1 Dec-21 1 Jan-22 1 Feb-22 1 1
4 Replies

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

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.

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

I've added the desired outcome on the initial post.
best response confirmed by Pratichi (Occasional Contributor)
Solution

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

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