Fix Excel SUMIF Errors Caused by Numeric and Text Concatenated Keys
Question details
Create consistent fixed-width text keys so SUMIF matches the intended records when a concatenated key includes numeric department codes, employee IDs, and dates.

- Product
- Microsoft Excel and WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user needs to sum values across worksheets by using a concatenated lookup key.
- Observed behavior
- SUMIF can match unintended records when numeric key components are stored or formatted differently between worksheets.
Identify the columns used for the department code, employee ID, and date on each worksheet. Confirm that both worksheets use the same source values and that the date values are real spreadsheet dates rather than text strings.
Build the concatenated SUMIF key as fixed-width text on both worksheets
Use this method when numeric-looking components in a concatenated key cause SUMIF to return totals for the wrong records.
Explicit TEXT formatting prevents values such as 12 and 0012 from being interpreted inconsistently. Use the same key formula, component order, and widths in the criteria data and the worksheet containing the SUMIF formula.
On each worksheet, add a helper column for the matching key, such as a column named Key beside the source data.
In the first key cell, enter a formula such as =TEXT(DepartmentCode,"0000")&TEXT(EmployeeID,"000000000")&TEXT(DateValue,"00000"). Replace DepartmentCode, EmployeeID, and DateValue with the appropriate cell references or named ranges.
Select the key formula cell and drag the fill handle down, or use Home > Fill > Down, so every row has a key created with identical formatting.
In the summary worksheet, use the formatted helper key as the SUMIF criterion, for example =SUMIF(Data!$A$2:$A$1000,A2,Data!$B$2:$B$1000), where Data column A contains the fixed-width keys and Data column B contains the values to add.

Check SUMIF range sizes and date values
Use these checks if the key formula is consistent but SUMIF still returns an incorrect result or no result.
Create consistent SUMIF keys with WPS Spreadsheets
WPS Spreadsheets lets you build fixed-width text keys with TEXT, combine fields with the ampersand operator, and use SUMIF to calculate totals from matching records across worksheets. Its familiar spreadsheet interface makes it easy to inspect formulas, validate date values, and correct range references.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the workbook containing the source and summary worksheets.
- 2. Add a helper key column: Insert a column beside the source data and label it Key so the concatenated value can be reviewed for every row.
- 3. Enter the fixed-width key formula: In the first helper cell, enter =TEXT(DepartmentCode,"0000")&TEXT(EmployeeID,"000000000")&TEXT(DateValue,"00000"), using your own cell references where needed.
- 4. Copy the formula to the matching worksheet: Create the same helper-key formula and component order on the worksheet used by the SUMIF criteria.
- 5. Update and verify SUMIF: Select the SUMIF cell, confirm its criteria and sum ranges align, then compare a known key and total to validate the result.

Frequently Asked Questions
Why does SUMIF return a total for the wrong concatenated key?
The key components may not be stored consistently. For example, a department code may be numeric on one sheet and text with leading zeros on another. Build both keys with the same TEXT formula and fixed widths.
How do I preserve leading zeros in a SUMIF key?
Wrap the value in TEXT with the required number of zeros. For a four-digit department code, use TEXT(A2,"0000"). Then concatenate it with the other formatted components.
Do SUMIF criteria range and sum range need to be the same size?
Yes. The criteria range and sum range must cover corresponding rows. For example, use A2:A1000 as the criteria range only with a sum range such as B2:B1000.
Can dates cause concatenated SUMIF keys not to match?
Yes. Text dates, date serial values, and dates containing times can create different keys. Ensure both sheets use real date values and, when times are present, use INT(DateCell) before formatting the date portion.




