Function Problems

How to Assign Excel Points from Drop-Down List Selections

Elise Williams Oct 15, 2026 871 views

Question details

The user wants to assign specific numerical points to text selections from a drop-down list (such as attendance statuses) and calculate totals for visible records only.

How to Assign Excel Points from Drop-Down List Selections

Product: Excel
Device & OS: not provided
Scenario: Creating an attendance tracker where statuses like Present, Tardy, Absent, and Left Shift Early are selected from a drop-down list, requiring automated point assignment and accurate filtered totals.
Observed behavior: Needs to automatically convert drop-down text selections into numerical scores based on scoring rules and sum only the visible rows when employee records are filtered.

Before you start

Ensure you have a separate lookup table set up in your workbook containing each exact drop-down status (e.g., Present, Absent) in one column and its corresponding point value in the adjacent column.

Solution 1Recommended

Use XLOOKUP and SUM to Assign Points

Combine the SUM and XLOOKUP functions to dynamically match text selections with point values and calculate the total score for a row.

By utilizing a lookup table, you make the scoring rules easy to maintain without constantly editing complex formulas.

The XLOOKUP function retrieves the corresponding points for each drop-down selection, while the SUM function adds them together.

Set up the lookup table

Create a simple table with statuses (e.g., Present, Tardy) in column I (e.g., I2:I4) and their respective points in column J (e.g., J2:J4).

Enter the formula

Click the cell where you want the total score for an employee and type the formula: =SUM(XLOOKUP(B2:D2,$I$2:$I$4,$J$2:$J$4,0)).

Adjust ranges and apply

Change B2:D2 to the range containing your drop-down selections. Press Enter, then drag the fill handle down to apply the formula to other rows.

Absolute References: Ensure you use absolute references (the $ sign) for your lookup table range so it does not shift when copying the formula down.

Solution 2

Calculate Totals for Filtered Rows Using SUBTOTAL

Use the SUBTOTAL function to sum the assigned points while ignoring any hidden or filtered rows.

Efficient Data Management with WPS

Easily Track Attendance and Assign Points with WPS Spreadsheet

WPS Spreadsheet provides comprehensive support for modern array formulas like XLOOKUP and dynamic functions like SUBTOTAL, making it the perfect tool for creating automated attendance trackers and managing drop-down point systems seamlessly.

1. Create Drop-Down Lists: Open WPS Spreadsheet, select your attendance cells, and navigate to 'Data' > 'Validation' to set up your drop-down lists.
2. Set Up Your Lookup Table: Enter your attendance statuses and corresponding point values into a dedicated area of your sheet.
3. Apply Scoring Formulas: Use the XLOOKUP and SUM formula to assign points, and use SUBTOTAL to calculate dynamically filtered totals.

Fully compatible with Microsoft Excel formulas, functions, and formatting.Seamless support for XLOOKUP, VLOOKUP, and SUBTOTAL for complex calculations.Built-in Data Validation features for easy drop-down list creation.Free, lightweight, and user-friendly interface for effortless data management.

microsoft office alternative - wps office

Frequently Asked Questions

What if my drop-down selection doesn't match the lookup table?

If a selection in your drop-down list does not exactly match the text in your lookup table, XLOOKUP will return an #N/A error. Ensure there are no leading or trailing spaces in either list.

How does SUBTOTAL(9, range) differ from the standard SUM function?

The standard SUM function adds all numbers in a range, including hidden rows. The SUBTOTAL function with the operation code '9' specifies that only visible rows should be summed, ignoring those hidden by an active filter.

Can I use VLOOKUP instead of XLOOKUP to assign points?

Yes, but VLOOKUP evaluates one cell at a time. To sum multiple columns of drop-down lists, XLOOKUP is more efficient because it can process an entire array (like B2:D2) natively, passing the results directly to the SUM function.

Excel & Data Problems