How to Make Microsoft Project Formulas Work for Summary Tasks
Question details
The user needs custom-field formulas to calculate properly for summary tasks, and wants to display resource names beside group summary Gantt bars without assigning resources directly to summary rows.
- Product
- Microsoft Project
- Device & OS
- not provided
- Scenario
- Setting up project schedules with custom fields and formulas, aiming to calculate data across both individual subtasks and grouped summary rows.
- Observed behavior
- Custom formulas calculate correctly on individual subtasks but fail to compute or display results on the summary task rows by default.
Ensure you have the appropriate permissions to modify custom fields in your Microsoft Project file. If you plan to use macros for resource displays, confirm that your project's Trust Center settings allow VBA execution.
Enable Custom Field Formulas for Task and Group Summary Rows
Configure the custom field properties so the formula calculates for summary rows instead of leaving them blank.
By default, Microsoft Project applies custom formulas only to subtasks. You must explicitly tell the program to apply the formula at the summary level using the Custom Fields dialog.
Go to the 'Project' tab on the main ribbon and click on 'Custom Fields' in the Properties group.
From the list, select the specific custom field (e.g., Text1, Number1, or Cost1) that contains your formula.
Under the 'Calculation for task and group summary rows' section, select the 'Use formula' radio button.
Click 'OK' to save the settings. The summary tasks will now display the calculated values based on your formula.
Display Subtask Resources Beside Summary Bars Using VBA
Since directly assigning resources to summary tasks is heavily discouraged, populate a custom text field with subtask resources using a VBA macro.
Manage Project Schedules Effortlessly with WPS Office
While Microsoft Project offers deep capabilities for complex scheduling, many teams find tracking tasks, resources, and budgets much easier using flexible spreadsheets. WPS Office provides a free, lightweight suite with highly compatible spreadsheet features, perfect for building Gantt charts, managing task lists, and calculating summary metrics without the need for complex VBA scripting.
- 1. Download WPS Office: Install the free WPS Office suite on your device.
- 2. Open WPS Spreadsheet: Create a new spreadsheet or open an existing project tracker template.
- 3. Manage Project Summaries: Use standard spreadsheet functions to easily group tasks, calculate summary data, and build visual Gantt charts.
Frequently Asked Questions
Why are my summary task custom fields completely blank in Microsoft Project?
By default, custom fields with formulas do not calculate for summary tasks. You must explicitly enable the 'Use formula' option under the 'Calculation for task and group summary rows' setting in the Custom Fields dialog.
Can I just assign a resource directly to a summary task to show their name?
While the software allows it, assigning resources directly to summary tasks is heavily discouraged by experts. It often leads to double-counting work hours, skewed cost calculations, and confusing resource allocation reports.
How do I show subtask resources on a summary Gantt bar?
Because you shouldn't assign resources directly to the summary task, you need to use a VBA macro to gather the subtask resource names and populate them into a custom Text field. You can then format the summary Gantt bar's text properties to display that custom Text field.
Does the 'Rollup' option work the same as 'Use formula' for summary rows?
No. 'Rollup' mathematically summarizes data from subtasks (like summing costs or finding the maximum date), whereas 'Use formula' applies the exact custom formula to the summary row using the summary row's own overarching data.
