How to Use SUMIF in WPS Office Spreadsheets to Match Names That Begin with a First Name
Question details
The user needs to calculate salary totals or averages for employees whose names begin with a specified first name, including records where initials or other characters follow the first name.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has an employee salary table with names in one column and salaries in another, and wants to aggregate values by first name using a criterion cell.
- Observed behavior
- The desired result is for SUMIF or AVERAGEIF to match the leading first name and ignore the rest of the name, such as initials after the first name.
Make sure the employee names and salary values are in adjacent columns, and place the first-name criterion in a separate cell such as A19. Confirm that the criterion cell contains only the first name with no leading or trailing spaces.
Use SUMIF with a Wildcard Criterion to Total Salaries by First Name
Use this method to sum salary amounts for all employees whose names start with the first name entered in a criterion cell.
The asterisk wildcard in SUMIF matches zero or more characters after the first name. This allows one formula to include names such as "Steve M.", "Steve P.", and other variations that begin with "Steve".
Click the cell where you want the total salary to appear, for example C19.
Type =SUMIF($A$2:$A$16,A19&"*",$B$2:$B$16) and press Enter. Here $A$2:$A$16 contains employee names, A19 contains the first-name criterion, and $B$2:$B$16 contains salaries.
Review the result to confirm that names followed by initials or extra characters are included in the total.
Use AVERAGEIF with the Same Wildcard to Calculate an Average Salary by First Name
Use this method when you need the average salary rather than the total for a specific first name.
Use Wildcard SUMIF and AVERAGEIF Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets supports the same wildcard criteria as Excel, so you can total or average salary data by first name without changing your formulas.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook that contains the employee names and salary data.
- 2. Enter the SUMIF wildcard formula: Select the cell for the total and type =SUMIF($A$2:$A$16,A19&"*",$B$2:$B$16), adjusting the ranges to match your sheet.
- 3. Add an AVERAGEIF formula when needed: Select another cell and type =AVERAGEIF($A$2:$A$16,A23&"*",$B$2:$B$16), where A23 contains the first name.
- 4. Verify names and totals: Check the results against names such as Steve M. and Steve P. to confirm the wildcard includes all entries beginning with the first name.
Frequently Asked Questions
Does WPS Spreadsheets support the SUMIF wildcard technique used in Excel?
Yes. WPS Spreadsheets supports SUMIF with wildcard criteria, so a formula like =SUMIF($A$2:$A$16,A19&"*",$B$2:$B$16) works the same way it does in Microsoft Excel.
How can SUMIF match names that start with a first name but have initials afterward?
Use the criterion cell followed by an asterisk wildcard, such as A19&"*". The asterisk matches any characters after the first name, including initials like Steve M. or Steve P.
Which ranges should be absolute in the SUMIF formula?
Use absolute references for the name range and salary range, such as $A$2:$A$16 and $B$2:$B$16. This lets you copy the formula without shifting the ranges.
How do I calculate an average salary for a first name instead of a total?
Use AVERAGEIF with the same wildcard technique, for example =AVERAGEIF($A$2:$A$16,A23&"*",$B$2:$B$16), where A23 contains the first name.
Will extra spaces before the first name break the SUMIF matching?
Yes. Leading or trailing spaces in the name cells or criterion cell can prevent a match, so clean the data or use TRIM to remove unwanted spaces before applying SUMIF.
