Function Problems

How to Sum Excel Payments by Employee Number in Mixed Text

Anum Mushtaq Oct 23, 2026 870 views

Question details

The user needs to total payments for specific employee numbers that are embedded within mixed text formats, such as names or email addresses.

How to Sum Excel Payments by Employee Number in Mixed Text

Product: Excel
Device & OS: not provided
Scenario: Calculating total payments for employees when their ID numbers are mixed with other text strings.
Observed behavior: The target employee number appears either alone or included inside other text (e.g., 'Sally Mae (12345@WORKPLACE)'), requiring wildcard criteria to properly match and sum the associated values.

Before you start

Ensure that your employee numbers are formatted consistently as either text or numbers across your dataset to avoid mismatch errors during the calculation.

Solution 1Recommended

Use the SUMIF Function with Wildcard Criteria

The most efficient way to sum values based on a partial text match is by using the SUMIF function combined with asterisk (*) wildcards to search for the ID within any string.

The asterisk (*) wildcard in Excel represents any number of characters. By concatenating the asterisk before and after your cell reference, you instruct Excel to find the employee number regardless of the text that surrounds it.

Select the target cell

Click on the blank cell where you want the total payment calculation to be displayed.

Enter the SUMIF formula

Type the formula =SUMIF($A$2:$A$9,"*"&K2&"*",$C$2:$C$9). In this formula, $A$2:$A$9 is the range containing the mixed text, K2 is the cell containing the specific employee number you want to find, and $C$2:$C$9 is the range containing the payment amounts.

Calculate the result

Press the Enter key. Excel will automatically sum all payments where the specified employee number appears anywhere within the text in column A.

Use the SUMIF Function with Wildcard Criteria

Direct Text Entry: If you do not want to reference a cell like K2, you can type the employee number directly into the formula by enclosing it in quotes with the asterisks, such as =SUMIF($A$2:$A$9,"*12345*",$C$2:$C$9).

Solution 2

Use an Excel Table with a Total Row

If you prefer a visual and interactive approach rather than writing complex formulas, you can format your data as a table, filter for the specific employee, and use a Total Row.

Advanced Data Calculation

Use WPS Spreadsheet to Sum Data with Wildcards

WPS Spreadsheet fully supports advanced functions like SUMIF and wildcards, making it incredibly easy to extract and calculate totals from mixed text data. It provides a familiar, fast interface for managing complex financial and employee records.

1. Open your file: Launch WPS Spreadsheet and open the document containing your employee and payment data.
2. Select a calculation cell: Click on the blank cell where you wish to display the summed total.
3. Input the SUMIF formula: Type your SUMIF formula using the ampersand and asterisks to create the wildcard search, for example: =SUMIF(A:A, "*"&K2&"*", C:C).
4. Execute the calculation: Press Enter to instantly view the calculated payment total for the specified employee number.

Fully compatible with Microsoft Excel formulas, functions, and wildcards (.xlsx format).Lightweight software that processes large datasets and complex string searches quickly.Includes a comprehensive built-in formula library and user-friendly function arguments dialog.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIF formula returning zero when using wildcards?

This usually happens if there is a data type mismatch. If the employee number in your reference cell is stored strictly as a number, but the source data is mixed text, the wildcard concatenation might fail. Try converting your reference cell to text or double-check that there are no trailing spaces.

Can I use multiple criteria to sum payments with mixed text?

Yes. If you need to check multiple conditions (such as the employee number within text AND a specific payment month), use the SUMIFS function instead of SUMIF. The wildcard syntax ("*"&Cell&"*") works exactly the same way for the criteria within SUMIFS.

Are wildcards case-sensitive in Excel functions?

No, wildcard searches within the SUMIF and SUMIFS functions are not case-sensitive. They will treat uppercase and lowercase letters identically when scanning through your mixed text data.

Excel & Data Problems