WPS Spreadsheet: Populate Nutrition Totals from Excel Drop-Down Meal Selections
Question details
The user needs to calculate nutrition totals from meal numbers selected in spreadsheet drop-down cells.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A meal tracker stores meal IDs in a database with nutrition values and uses drop-down selections to choose multiple meals.
- Observed behavior
- The nutrition total cells need to return the matching calories, protein, carbohydrates, and fat for all selected meal IDs.
Confirm that the meal IDs in the database and the drop-down selections use the same values and format. Check that the nutrition columns have the same row range as the meal ID range before entering the formula.
Use a lookup-based MMULT formula for all selected meals
Use this method when several drop-down cells contain meal IDs and each nutrition column needs a matching total.
The formula compares each database meal ID with every selected meal ID, converts the matches to 1 or 0, and multiplies them by the nutrition values. Fill the formula across the nutrition columns so the referenced nutrition range changes from calories to protein, carbohydrates, and fat.
In WPS Spreadsheet, click the first nutrition total cell for the selected meals, such as the calories total cell.
Enter =SUM(MMULT(TRANSPOSE(N($B$4:$B$13=$I4:$M4)),E$4:E$13)) and press Enter. Replace the example ranges if your meal IDs, selections, or nutrition values are in different cells.
Drag the fill handle across the adjacent total cells, or use Home > Fill > Right, so the nutrition range changes from E$4:E$13 to the corresponding protein, carbohydrate, and fat columns.
Change a meal number in one of the drop-down cells and verify that the related nutrition totals update automatically.
Calculate each nutrition column with SUMPRODUCT and MATCH
Use this alternative when you prefer a separate, readable formula for each nutrition total or your spreadsheet setup does not support the MMULT approach.
Build an Automatic Meal Nutrition Tracker in WPS Spreadsheet
WPS Spreadsheet lets you combine drop-down selections with lookup, SUMPRODUCT, and array formulas to calculate nutrition totals automatically. Its familiar spreadsheet interface makes it easy to maintain the meal database and export or share the tracker in Microsoft Excel-compatible formats.
- 1. Create the meal database: Open WPS Spreadsheet and place meal IDs in one column with calories, protein, carbohydrates, and fat in adjacent columns.
- 2. Add meal selections: Select the meal-choice cells and use Data > Dropdown to create selectable meal numbers.
- 3. Enter the total formula: Click the first nutrition total cell and enter the MMULT or SUMPRODUCT formula using your meal ID, selection, and nutrition ranges.
- 4. Fill across and verify: Use the fill handle to copy the formula across the remaining nutrition columns, then change a drop-down selection to test the results.
Frequently Asked Questions
Which WPS Spreadsheet formula totals nutrition for multiple selected meal IDs?
Use =SUM(MMULT(TRANSPOSE(N($B$4:$B$13=$I4:$M4)),E$4:E$13)) in the first total cell, then fill it across the nutrition columns. Adjust the ranges to match your worksheet.
Why are my nutrition totals incorrect after copying the formula?
Check that the meal ID range and nutrition range cover the same rows and that the selected meal cells use the same number or text format as the database IDs. Also verify that the nutrition column reference changes correctly when filling across.
Can I calculate only calories with SUMPRODUCT?
Yes. Use =SUMPRODUCT(--ISNUMBER(MATCH($B$4:$B$13,$I4:$M4,0)),E$4:E$13), where E4:E13 contains calories. Replace the nutrition range with the appropriate column for other nutrients.
What happens when a selected meal ID does not exist in the database?
The unmatched ID contributes zero to the total. Check for spelling, spacing, or number-versus-text differences if a meal should be included but is missing.
