Sum Quantities by Product Code in Excel
Question details
Identify product types by characters within their codes and total the quantities for each type.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Product codes and quantities are stored in separate columns, and characters three and four identify the product type or size.
- Observed behavior
- The desired result is a quantity total for each matching product code segment.
Confirm that product codes and their quantities are on the same rows and that quantities are numeric. The examples assume codes are in A2:A100 and quantities are in B2:B100.
Use SUMIFS to total quantities for one product code
Use this method when you know the two-character code to match, such as 23.
Click an empty cell outside the product code and quantity ranges.
Enter =SUMIFS(B2:B100,A2:A100,"??23*") and press Enter. Replace 23 with the two characters that identify your product type.
Review the codes in column A: each question mark matches one character, 23 must appear in positions three and four, and the asterisk matches any remaining characters.

Create a dynamic summary of product code segments
Use this method in Microsoft 365 to list distinct two-character codes and total each group.
Sum matching product quantities in WPS Spreadsheets
Use SUMIFS and wildcards in WPS Spreadsheets to total quantities for a product type identified by characters in its code.
- 1. Open your spreadsheet: Open WPS Spreadsheets and choose File > Open to select your Excel workbook.
- 2. Check the columns: Locate the product codes and quantities. For this example, put codes in A2:A100 and quantities in B2:B100.
- 3. Enter the formula: Select an empty cell and enter =SUMIFS(B2:B100,A2:A100,"??23*"). Replace 23 with the product identifier you need.
- 4. Review and save: Press Enter to view the total, then choose File > Save As if you want to save the workbook in Excel format.

Frequently Asked Questions
How can I total a product code entered in another cell?
If the two-character identifier is in D2, enter =SUMIFS(B2:B100,A2:A100,"??"&D2&"*") in an empty cell.
Why does the formula use two question marks?
In SUMIFS criteria, each question mark matches one character. Two question marks before 23 place the match at the third and fourth characters.
Can I use this formula with named ranges?
Yes. If Products and Quantities refer to corresponding ranges, enter =SUMIFS(Quantities,Products,"??23*").
Why is my quantity total zero?
Check that the identifier appears in the expected positions in column A and that quantities in column B are numeric. Also confirm that the SUMIFS ranges cover corresponding rows.




