How to Sum Personnel for Multiple Drop-Down Selections in Excel
Question details
The user needs to calculate the total personnel count by summing values for multiple office locations that are selected within a single cell, separated by commas.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Working with data where a single cell contains multiple comma-separated values from a drop-down list (e.g., "Austin, Tampa"), and a total sum needs to be calculated based on a separate reference table.
- Observed behavior
- The user wants to dynamically look up and sum the numerical values for all the text items listed within the same cell string without manually parsing them.
Ensure your reference table is properly formatted (e.g., as an Excel Table named "Office") and that the delimiter used in your drop-down selections matches the one in your formula exactly.
Use SUM, XLOOKUP, and TEXTSPLIT Functions
This method uses dynamic array functions to split the comma-separated string, look up each individual value, and sum the total in one go. It is ideal for Excel 365 and modern spreadsheet software.
This approach leverages the TEXTSPLIT function to turn the single-cell string into an array of individual items. XLOOKUP then finds each item in the reference table, and SUM adds up the returned values.
Click on the cell where you want to display the total personnel sum for the selected allocations.
Type the following formula into the formula bar: =SUM(XLOOKUP(TEXTSPLIT([@Allocations],", "),Office[Office],Office[# of Personnel],0))
Press Enter. The TEXTSPLIT function will separate the combined locations (e.g., "Austin, Tampa") using the comma and space delimiter.
XLOOKUP evaluates each separated location against the Office[Office] column and returns the corresponding personnel counts, which are then immediately totaled by the SUM function.
Use SUMPRODUCT, ISNUMBER, and SEARCH Functions (Alternative)
If you are using an older version of Excel that does not support TEXTSPLIT or XLOOKUP, you can use SUMPRODUCT to evaluate the cell contents against the entire reference table.
Easily Calculate Multiple Criteria Values with WPS Spreadsheet
WPS Spreadsheet offers a powerful, user-friendly environment for managing complex data tables and formulas. With support for advanced lookup features and array formulas, summarizing data from multiple drop-down selections is seamless and fast.
- 1. Open your data: Launch WPS Spreadsheet and open the workbook containing your drop-down selections and reference table.
- 2. Format reference data: Ensure your reference data is properly organized with clear column headers for the lookup values and the return values.
- 3. Input the array formula: Select the output cell and type the SUM and XLOOKUP combination formula directly into the formula bar.
- 4. Get instant results: Press Enter to instantly view your aggregated total for the combined selections across your dataset.
Frequently Asked Questions
Why am I getting a #NAME? error when using TEXTSPLIT?
The #NAME? error occurs if your version of Excel or spreadsheet software does not support the TEXTSPLIT function, which was introduced in newer versions like Microsoft 365. If you are on an older version, consider using the SUMPRODUCT and SEARCH alternative method provided in this guide.
How do I handle cells that use just a comma without a space as a delimiter?
You need to adjust the delimiter argument in the TEXTSPLIT function. Change the ", " (comma and space) to "," (just a comma) in your formula so it matches the exact formatting used in your drop-down cell.
What happens if an office name in the drop-down is not found in the reference table?
The formula provided includes 0 as the fourth argument of the XLOOKUP function, which stands for the 'if_not_found' parameter. If a selected office is missing from the reference table, XLOOKUP will safely return 0, ensuring the SUM function still works without returning an error.
