How to Sum Selected Cells in Microsoft Project Using a VBA Macro
Question details
The user needs to calculate the total sum of specific selected numeric fields in Microsoft Project.
- Product
- Microsoft Project
- Device & OS
- not provided
- Scenario
- The user selects multiple numeric cells across different tasks with the mouse and needs to see their total sum.
- Observed behavior
- Microsoft Project does not provide a status bar sum indicator for selected numeric fields like Excel does, requiring a custom script to calculate the total.
Ensure you have the Developer tab enabled in Microsoft Project so you can access the Visual Basic for Applications (VBA) editor.
Create and Run a Custom VBA Macro to Sum Cells
Use a custom VBA script to iterate through your selected tasks and fields, calculate the total sum, and display it in a message box.
Since Microsoft Project is fundamentally a scheduling tool, it lacks quick spreadsheet-like calculation features on the status bar. Implementing a VBA macro is the most direct way to sum cells you select with your mouse.
In Microsoft Project, navigate to the Developer tab or View tab, click on 'Macros', and select 'Visual Basic' to open the VBA Editor.
In the VBA Editor, click 'Insert' from the top menu and select 'Module' to create a blank workspace for your code.
Copy and paste the following code into the module window: Sub SumSelected() Dim t As Task Dim Fld As Variant Dim NumSum As Single For Each t In ActiveSelection.Tasks For Each Fld In ActiveSelection.FieldIDList NumSum = NumSum + t.GetField(FieldID:=Fld) Next Fld Next t MsgBox "Selected Sum = " & NumSum End Sub
Close the VBA editor and return to your project view. Use your mouse to click and drag, or hold the 'Ctrl' key to select the specific numeric cells you want to sum.
Go to 'Macros' > 'View Macros', select 'SumSelected' from the list, and click 'Run'. A pop-up message box will appear displaying the calculated total.
Calculate Sums in a Spreadsheet Tool Before Migrating
If you prefer not to use VBA or have complex calculation needs, you can manage your simple numeric tasks in a spreadsheet application and then migrate the data.
Manage Your Data Easily with WPS Office
While Microsoft Project requires custom macros for simple summations, a dedicated spreadsheet tool is often better for data calculations. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office. With WPS Spreadsheet, you can instantly see the sum of selected cells in the status bar—no VBA required—and it perfectly supports Microsoft Excel formats.
- 1. Download and Install: Visit the official WPS Office website to download the free installation package for your operating system.
- 2. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to open a new or existing workbook.
- 3. Calculate Instantly: Select any numeric cells and instantly view the sum at the bottom-right corner of your screen without writing any code.
Frequently Asked Questions
Why doesn't Microsoft Project show the sum of selected cells at the bottom of the screen?
Unlike Excel, Microsoft Project is designed primarily for task scheduling and timeline management rather than flexible data calculation. It focuses on rolling up project costs and durations automatically, so it lacks the quick, arbitrary status bar calculation features found in spreadsheet software.
Can I assign a keyboard shortcut to the sum macro in MS Project?
Yes. After creating the macro, go to the Macros dialog box (View > Macros), select your 'SumSelected' macro, click 'Options', and assign a custom keyboard shortcut for faster access.
Does this VBA macro work for non-numeric fields?
No, the provided VBA macro uses mathematical addition (NumSum + value). If you select text or date fields, the macro will fail or return an error. Always ensure that only numeric fields are selected before running the macro.
How do I select multiple specific cells in Microsoft Project before running the macro?
You can hold down the 'Ctrl' key on your keyboard while clicking on different numeric cells in your task list. This allows you to select multiple non-adjacent fields to be summed up by the macro.
