Ask the Experts: Counting Task Occurrences with Microsoft Project

Angelo of Rochester, NY: When managing project schedules I need to produce a count of various tasks. For example, how many late tasks are in a schedule, how many critical tasks, how many milestones, etc. Typically, I have copied or exported data to Excel and used Excel to calculate the counts for me. Is there a way to have MS Project count these tasks?

Ellen explains: Accumulating a count of recurrences in a column value can have multiple uses for reporting in a project schedule. To make a count occur and accumulate you will have to create a customized field with a formula, insert that column into a task table, and then apply groupings and filters. Below are examples with the steps needed to create this type of report.

All versions of MS Project have the ability to create customized fields with formulas. You will first create a customized number field, apply “1” into the formula, and set a roll-up value to sum the column value.

1. Using MS Project 2010, click on: Project| Customize Fields.
Using earlier levels of MS Project, click on: Tools| Customized Fields| Fields.

2. To create and rename a customized Number field to be called “Count”:

Choose Type | Number.
Select Number 1 (or any unused number field).
Click Rename | Enter “Count”| Click OK. (See below.)

3. After the field has been created, click on Formula in the “Custom attributes” section and click OK to accept the warning message that all existing values will be discarded from the selected field.

4. Enter 1 for the formula in the box at the top of the view, click OK to close the box and OK again to accept another warning message. This action will insert a “1” for each detail and milestone task in the “Count” field for your project.

Note: In the view below, the vertical line to the right of the number 1 is the cursor.

5. The last step is to click on Rollup and select Sum in the “Calculation for task and group summary rows” section. Then click OK to close the Customized field box.

Insert the “Count” column into any task table view and you will note the accumulated value on the summary rows. In the example below, Outline Level 1 was applied and you will notice that there are five tasks in the Scope section and nine tasks in the Analysis/Software Requirements section. The Project Summary task Software Development is displaying a total of 74 detail and milestone tasks contained in the project schedule and is not counting summary tasks.

If you want to know how many critical tasks are in your project schedule, apply the “Critical” grouping and adjust the Outline Level to Outline Level 1; you can easily see the count of the number of tasks in each category.

To clear the Grouping

Using MS Project 2010, click on View| No Group.
Using earlier MS Project versions, select Project| Group by| No Group.

In the example below I have created a customized field called “Location” and populated it with some city names. I then created a Grouping called “Location” and applied the new group. To create the new group:

1.Using MS Project 2010, click on: View| Group| More Groups.
Using earlier levels of MS Project, click on: Project Group by| More Groups | New.
2.Name: Location
3.Group by: Location
4.Click OK to close the box.

To view the numbers of tasks occurring at each location, apply the grouping.

Using MS Project 2010, click on: View| Group| Location.
Using earlier levels of MS Project, click on: Project| Group by| Location.

More task count information can be gained by applying filters. Some useful filters for this purpose could be “Late tasks,” “Critical tasks,” “Uncompleted tasks,” “Milestone tasks,” “Over budget tasks,” “Should have started tasks,” etc. After a filter is applied you can then apply an outline level to view totals.

Another filtering idea is to filter by value within a column. For example, you might want to know how many tasks contain the word “Develop” by location. Using the “Location” group created above, apply the group and apply a custom filter that contains the word “Develop” and then apply Outline Level 1. Notice in the example below that the number of occurrences per location has changed.

One more idea is to group tasks by week and find out how many tasks should be starting in weekly timeframes.

1.Using MS Project 2010, click on the Start column and select Group by| Week to create the grouping quickly. You may also click on Project Group by| New Group by and create a grouping object.

Using earlier levels of MS Project, click on Project Group by| More Groups | New.

2.Name: Weekly by Start date.
3.Group by: Start.
4.Define group intervals: Each Week.
5.Click OK to close the Define Interval box.
6.Click OK to close the Group box.

Below is an example of a weekly grouping with the count for number of tasks scheduled by week.

To keep the customized fields, groupings, filters, and all customized objects, copy the objects into your Global.mpt using the Organizer function.

1. Using MS Project 2010, click on File| Info| Organizer.
   Using earlier levels of MS Project, click on Tools| Organizer.
2. Click on the tab along the top for the type of object you wish to copy.
3. Click on the object name on the right side of the view.
4. Click Copy to copy the object to your Global.mpt file.
5. Repeat until all objects are copied.
6. Click Cancel to close the Organizer box.

TIP: Add the Clear Group button to the Quick Access bar in MS Project 2010. This button can be found in the All Commands section of the Customize Quick Access bar.

1.On the right side of the Quick Access bar click on the down arrow.
2.Click on More Commands.
3.In Choose commands from select All Commands.
4.Scroll down to Clear Group and click on it.
5.Click Copy.
6.Click OK to close the box.

You can add more buttons to the Quick Access bar if the bar is below the ribbon bars.

Thanks to Angelo Arcoleo, PMP and Master Scheduler, Project Controls Engineer, ITT Exelis, President of MPUG Chapter Rochester NY for his input to this article.