How to Calculate and Display a Total in Microsoft Forms
Question details
The user wants to know if Microsoft Forms can dynamically calculate and display a total price or numerical total based on the options a respondent selects.
- Product
- Microsoft Forms
- Device & OS
- not provided
- Scenario
- Creating an order form, questionnaire, or survey where selected items have assigned values that need to be tallied.
- Observed behavior
- Microsoft Forms does not currently provide built-in formulas or tools for calculating and displaying a real-time total based on selected items and their prices.
Because Microsoft Forms cannot compute totals internally, ensure you have spreadsheet software installed on your device to process the downloaded response data.
Export Form Responses to Excel for Calculation
Since Forms cannot calculate totals internally, you can export the submitted data to a spreadsheet and use standard formulas to find the total.
While respondents will not see the total while filling out the form, exporting the data allows the form owner to accurately compute the amount due using spreadsheet formulas.
Log into Microsoft Forms, open your specific form, and navigate to the 'Responses' tab located at the top of the page.
Click on the 'Open in Excel' button. This will download a spreadsheet containing all submitted responses.
Open the downloaded file in your spreadsheet software. Create a new column titled 'Total' next to the response data.
Use standard lookup formulas like =VLOOKUP() to assign prices to selected text items, and use the =SUM() function to calculate the final amount due for each respondent.
Create an Interactive Questionnaire in Power Apps
For an interactive calculation where users see the total immediately, use Microsoft Power Apps to build a custom form with calculated fields.
Process Your Form Data Seamlessly with WPS Office
While Microsoft Forms cannot calculate totals directly, you can effortlessly export your responses and process them using WPS Spreadsheet. WPS Office provides a lightweight, highly compatible, and free alternative to Microsoft Office for all your data calculation needs.
- 1. Install WPS Office: Download and install WPS Office for free from the official website.
- 2. Open exported data: Launch WPS Spreadsheet and open the Excel file downloaded from Microsoft Forms.
- 3. Calculate your totals: Use the formula bar to input SUM and VLOOKUP functions to accurately process your respondents' totals.
Frequently Asked Questions
Can I assign points to calculate a total score instead of a price?
Yes. If you create a 'Quiz' instead of a standard 'Form' in Microsoft Forms, you can assign point values to correct answers. The system will automatically calculate and display the total score to the user upon submission.
Will Microsoft Forms add a built-in calculation feature for prices?
Currently, Microsoft Forms does not support internal calculations for order forms. Users are encouraged to submit feature requests through the Microsoft Feedback portal to suggest this addition.
How can I make an interactive form where totals update in a spreadsheet?
You can build your questionnaire directly in WPS Spreadsheet or Microsoft Excel Online. By sharing the spreadsheet link with users, they can input their quantities directly into designated cells, and predefined SUM formulas will display the total immediately.
