Function Problems

How to Automatically Total Excel Categories from a Drop-Down List

Maira Mehtab Sep 20, 2026 868 views

Question details

The user wants to automatically calculate updating totals for monthly income and expenses using a category drop-down column without having to sort entries or manually change formulas.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking and summarizing financial data or expenses based on dynamic categorizations.
Observed behavior: The user needs a goal state where selecting a category from a drop-down list automatically calculates and expands totals for new rows seamlessly.

Before you start

Ensure your data set contains clear headers, such as 'Category' and 'Amount', and verify that your drop-down list categories perfectly match the text in your main data column.

Solution 1Recommended

Use the SUMIF Function for Automatic Category Totals

Apply the SUMIF function with absolute references to automatically total amounts based on a drop-down category selection.

The SUMIF function is perfect for adding values that meet a single criteria. By referencing your drop-down cell as the criteria, the formula dynamically calculates totals whenever a new category is selected.

Identify Data Ranges

Assume your main category data is in cells D2:D15 and the corresponding amounts are in C2:C15. Suppose the drop-down list for the category you want to total is located in cell E2.

Enter the SUMIF Formula

Select the cell where you want the total to appear. Type the formula: =SUMIF($D$2:$D$15,E2,$C$2:$C$15).

Apply and Fill Down

Press Enter to execute the formula. If you have multiple drop-down cells for different categories, use the fill handle to drag the formula down for each corresponding category.

Using Absolute References: Locking the data ranges with dollar signs (e.g., $D$2:$D$15) ensures your formula calculates correctly even if you copy it to other cells.

Solution 2

Convert Data to an Excel Table for Expanding Totals

Use an Excel Table combined with SUMIF to ensure any newly added rows are automatically included in your category totals.

Advanced Data Analysis

Calculate Dynamic Totals Easily with WPS Spreadsheet

WPS Spreadsheet seamlessly supports advanced functions like SUMIF, structured Table references, and Data Validation, making it incredibly easy to track expenses and automate categorized totals.

1. Create Your Drop-Down List: Navigate to the 'Data' tab, click 'Validation', choose 'List', and define your categories.
2. Format Data as a Table: Highlight your data, go to 'Home' or 'Insert', and select 'Format as Table' to enable dynamic ranges.
3. Calculate Using SUMIF: Input the =SUMIF() formula into your summary cell, referencing your Table columns and drop-down cell.

Fully compatible with Microsoft Excel functions like SUMIF and SUMIFS.Easily create drop-down lists with the Data Validation tool.Convert data to Tables for auto-expanding formula ranges.Lightweight, free-to-use, and highly intuitive interface.

microsoft office alternative - wps office

Frequently Asked Questions

How do I create a drop-down list for my categories?

Select the cell where you want the drop-down menu. Go to the Data tab, click on Data Validation, select 'List' under the Allow criteria, and then type or highlight the source of your categories.

Why isn't my SUMIF formula updating when I add new rows?

If you are using fixed ranges (like $D$2:$D$15), new data outside this range won't be calculated. You must either manually adjust the formula's range or, ideally, convert your data to an Excel Table (Ctrl+T) so the range expands automatically.

Can I use SUMIFS instead of SUMIF for this?

Yes. While SUMIF is great for a single condition (like category), SUMIFS allows you to add multiple criteria, such as calculating totals for a specific category AND a specific month.

Does WPS Office support Excel structured table references?

Absolutely. WPS Spreadsheet fully supports Microsoft Excel's structured table references, meaning your dynamic SUMIF formulas will work seamlessly when opening your workbook in WPS.

Excel & Data Problems