Function Problems

Excel Formula to Assign Option Values and Calculate Employee Totals

Elise Williams Sep 28, 2026 869 views

Question details

The user needs to assign specific numerical values to text codes (e.g., CO=1, LA=0.5, EO=0.5) to calculate daily employee totals, which will later be fed into a pivot table.

How to Assign Custom Values to Text and Calculate Employee Totals in Excel

Product: Excel
Device & OS: not provided
Scenario: Calculating employee attendance or specific operational totals where text strings correspond to numerical values across daily log entries.
Observed behavior: The user needs a reliable formula to automatically translate text options into numbers and sum them across rows for each employee.

Before you start

Ensure you know which daily entry columns contain your data (e.g., B2:H2). If your version of Excel does not support modern dynamic array functions like XLOOKUP, you can use the COUNTIF alternative provided.

Solution 1Recommended

Calculate Totals Using SUM and XLOOKUP

Create a mapping table for your custom values and use a combined SUM and XLOOKUP formula to calculate employee totals efficiently.

This method is highly scalable. By keeping your assigned values in a dedicated lookup table, you can easily change the values later without needing to rewrite your formulas.

Create a mapping table

In an empty area of your sheet (e.g., K2:L4), list your text codes (CO, LA, EO) in one column and their corresponding numerical values (1, 0.5, 0.5) in the adjacent column.

Enter the XLOOKUP formula

Select the cell where you want the employee's total to appear. Type the formula =SUM(XLOOKUP(B2:H2,$K$2:$K$4,$L$2:$L$4,0)), replacing B2:H2 with your actual daily entry range.

Apply to all rows

Press Enter to calculate the first total. Click the bottom-right corner of the target cell and drag the fill handle down to apply this calculation to all employees.

Dynamic Updates: Using a reference table allows you to update the numerical weight of any code (like changing LA from 0.5 to 1) instantly across all calculations.

Solution 2

Calculate Totals Using COUNTIF (For Older Excel Versions)

If your version of Excel does not support XLOOKUP, you can calculate the totals by counting the occurrences of each text code and multiplying them by their respective values.

Powerful Spreadsheet Solution

Seamlessly Calculate Data with WPS Spreadsheet

WPS Spreadsheet offers full support for advanced array formulas like XLOOKUP and SUM, allowing you to easily assign values and calculate employee totals. It is lightweight and highly compatible with Microsoft Excel.

1. Download WPS Office: Install WPS Office Free from the official website and open WPS Spreadsheet.
2. Open your worksheet: Load your employee tracking document into the software.
3. Apply the calculation formula: Enter the exact same =SUM(XLOOKUP(...)) or =COUNTIF(...) formula, as WPS Spreadsheet perfectly supports all Excel functions.

Fully compatible with Microsoft Excel formulas, including XLOOKUP and COUNTIF.Lightweight and fast, ideal for managing large monthly datasets and pivot tables.Free to use with a familiar, user-friendly interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning a #N/A error?

A #N/A error usually means a text entry in your daily log doesn't match the lookup table. Ensure the 'if_not_found' argument in your XLOOKUP formula is set to 0, and check for hidden spaces in your data cells.

Can I link these totals directly to a Pivot Table?

Yes. Once you calculate the totals in your source data rows, you can insert a Pivot Table and select this new total column as one of your value fields to easily summarize monthly data by employee.

Is there a simpler way if I only have two or three codes?

If you only have a few text codes, using the COUNTIF formula (e.g., =COUNTIF(Range, "CO")*1 + COUNTIF(Range, "LA")*0.5) is simpler as it does not require setting up a separate mapping table on your sheet.

Excel & Data Problems