Others

How to Calculate Costs from Multiple-Choice Columns in Microsoft Lists

WPS Content Manager Oct 12, 2026 868 views

Question details

The user needs to calculate a total cost based on multiple selected values from a choice column, but native formulas fail to process the multiple selections.

How to Calculate Costs from Multiple-Choice Columns in Microsoft Lists

Product: Microsoft Lists
Device & OS: not provided
Scenario: Attempting to use IF, SUMIF, or similar formulas inside a calculated column to extract and evaluate multiple selected replacement types and compute a total cost.
Observed behavior: Microsoft Lists calculated columns cannot reliably extract or evaluate multiple selected values from a multiple-choice column, rendering standard formula calculations invalid.

Before you start

Ensure you have the necessary permissions in your Microsoft 365 environment to modify list settings, create Power Automate flows, or customize forms with Power Apps.

Solution 1Recommended

Use Power Automate to Calculate and Update Costs

Since native calculated columns do not support multiple-choice extraction, Power Automate provides a robust way to iterate through selected choices and compute totals automatically.

Power Automate can run in the background every time an item is created or modified. It can loop through the array of selected choices, assign a price to each, and update the list item with the final total.

Create a new automated flow

Log into Power Automate and create an Automated Cloud Flow triggered by 'When an item is created or modified' in SharePoint/Microsoft Lists.

Initialize a variable for the total cost

Add an 'Initialize variable' action, name it 'TotalCost', set its type to Float (or Integer), and assign it an initial value of 0.

Iterate through the selected choices

Add an 'Apply to each' action and select the dynamic content of your multiple-choice column. Inside the loop, add a 'Switch' or 'Condition' action to check the current choice value.

Increment the total cost

For each matching choice in your Switch action, use the 'Increment variable' action to add the specific price of that choice to your 'TotalCost' variable.

Update the list item

Outside the loop, add an 'Update item' action. Pass the original item's ID and write the final 'TotalCost' variable into a separate Currency or Number column in your list.

Use Power Automate to Calculate and Update Costs

Flow Efficiency: To prevent infinite trigger loops, make sure to add Trigger Conditions to your flow so it only runs when the specific multiple-choice column changes, rather than when the flow updates the total.

Solution 2

Build a Power Apps Form to Handle the Logic

If users interact with your list via forms, Power Apps can dynamically calculate totals in real-time based on their selections before the data is even saved.

Solution 3

Workaround: Use Single-Choice Columns

If you do not strictly need multiple selections per row, converting the column to allow only a single choice restores native formula functionality.

Free Microsoft Office alternative

Manage Cost Tracking and Complex Formulas with WPS Office

While Microsoft Lists requires complex workarounds like Power Automate to handle multiple-choice calculations, managing your inventory and cost tracking in a dedicated spreadsheet is often much simpler. WPS Office is a lightweight, free alternative to Microsoft Office that allows you to easily manage dropdowns, data validation, and complex SUMIF array formulas to track costs effortlessly.

Fully compatible with Microsoft Excel (.xlsx) formats and formulasFree to use with a familiar, highly intuitive user interfaceHandles advanced array formulas, VLOOKUP, and SUMIF without the need for external automation toolsLightweight design ensures fast loading and seamless processing of large cost-tracking datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't the IF formula work with multiple-choice columns in Microsoft Lists?

Microsoft Lists calculated columns treat multiple-choice fields as complex array objects rather than standard text strings. Because native formulas like IF, SUMIF, and FIND cannot parse or iterate through arrays natively, they fail to evaluate multiple selections.

Can I request Microsoft to add calculated column support for multiple choices?

Yes, you can submit feature requests directly through the SharePoint and Microsoft Lists Feedback Portal. Microsoft's product teams actively monitor this portal to prioritize future updates and native features based on user demand.

Is Power Automate included in my Microsoft 365 subscription for this workaround?

Yes, most Microsoft 365 enterprise, education, and business plans include standard Power Automate capabilities. This is fully sufficient to trigger a flow from Microsoft Lists and update items with calculated costs without requiring a premium license.

Office Quick Solutions