Function Problems

How to Use Multiple Dropdown Selections and Calculate Totals in Excel

John Wilson Oct 10, 2026 870 views

Question details

The user needs to create multiple dropdown menus to select specific items and automatically calculate the total statistics based only on those selected items.

How to Calculate Totals from Multiple Dropdown Selections in Excel

Product: Excel Online
Device & OS: not provided
Scenario: Creating an interactive spreadsheet (such as an RPG character sheet) where selecting different perks or items from multiple dropdowns automatically updates the overall total scores.
Observed behavior: Standard list filtering does not correctly calculate the SUM for individually selected items across multiple distinct groups or columns.

Before you start

Ensure your reference data (the item names and their corresponding numerical statistics) is organized cleanly in adjacent columns without empty rows, so the lookup formulas can reference them accurately.

Solution 1Recommended

Use Data Validation Dropdowns Combined with SUMIF

This solution sets up selectable dropdown lists and uses the SUMIF function to find and add together the values corresponding to the user's specific selections.

Instead of relying on basic list filtering, combining Data Validation with lookup formulas allows the spreadsheet to dynamically retrieve and sum values based on user input from multiple cells.

Create the Dropdown Lists

Select the cell where you want the user to make a choice. Go to the 'Data' tab on the ribbon and click 'Data Validation'. Under 'Allow', select 'List', and then highlight the range of cells containing your source item names (e.g., A3:A76).

Write the SUMIF Formula

In the cell where you want the total to appear, enter a SUMIF formula to pull the value for the selected item. Use the format =SUMIF(Source_Names_Range, Dropdown_Cell, Source_Values_Range). For example: =SUMIF($A$3:$A$76, $A$78, B$3:B$76).

Combine Multiple Selections

If you have multiple dropdowns representing different choices, add their respective SUMIF functions together in the same formula. For example: =SUMIF(A:A, G1, B:B) + SUMIF(A:A, G2, B:B) + SUMIF(A:A, G3, B:B).

Calculate Grand Totals Across Columns

Once your formula is working for the first statistics column, click and drag the fill handle (the small square at the bottom right of the cell) across the adjacent columns to copy the formula. You can then sum all those results at the bottom using a standard =SUM(B78:B93) formula.

Use Data Validation Dropdowns Combined with SUMIF

Apply Conditional Formatting: To take your interactive sheet further, highlight your final total cells, go to 'Home' > 'Conditional Formatting', and set a 'Greater Than' rule to automatically highlight the cell in red if a statistic exceeds its maximum allowed limit.

Effortless Spreadsheet Calculations

Build Interactive Spreadsheets Easily with WPS Spreadsheet

WPS Spreadsheet provides robust support for Data Validation, SUMIF, and conditional formatting, allowing you to build complex interactive calculators and custom sheets with ease.

1. Organize your data: Open WPS Spreadsheet and list your reference names and numerical values in structured columns.
2. Insert Dropdowns: Click on the 'Data' tab in the top ribbon and select 'Validation' to assign a List to your input cells.
3. Apply SUMIF Formulas: Use the formula bar to input your =SUMIF() equations, linking your reference lists to the newly created dropdown cells.
4. Auto-fill your calculations: Drag the bottom-right corner of your formula cell across adjacent columns to instantly populate the rest of your calculations.

Seamlessly handles SUMIF, SUMIFS, and complex array formulas100% compatible with Microsoft Excel (.xlsx) file formatsIntuitive Data Validation tools for quick dropdown creationLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why isn't my SUMIF formula updating when I change the dropdown selection?

This usually happens if your workbook calculation options are set to 'Manual'. Go to the Formulas tab and ensure 'Calculation Options' is set to 'Automatic'. Additionally, verify that your formula references exactly match the text in the dropdown list.

Can I use VLOOKUP instead of SUMIF for this setup?

Yes, if each item name in your source list is unique and only appears once, VLOOKUP or XLOOKUP will work perfectly to fetch the corresponding value. However, SUMIF is highly recommended if you want to avoid #N/A errors when a dropdown is left blank, as SUMIF simply returns 0.

How do I lock my formula ranges so they don't shift when I copy them?

Use absolute cell references by adding dollar signs ($) to your range coordinates in the formula. For example, change A3:A76 to $A$3:$A$76. You can quickly toggle this by highlighting the cell reference in the formula bar and pressing F4.

Is it possible to calculate totals based on multiple conditions at once?

Yes. If your calculation depends on more than one criteria (for example, selecting an item name and its specific level), use the SUMIFS function instead. SUMIFS allows you to specify multiple criteria ranges and corresponding criteria in a single formula.

Excel & Data Problems