Function Problems

Sum Quantities by Product Code in Excel

Steve KSteve K Nov 18, 2026 869 views

Question details

Identify product types by characters within their codes and total the quantities for each type.

Sum Quantities by Product Code in Excel
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.
Before you start

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.

Solution 1Recommended

Use SUMIFS to total quantities for one product code

Use this method when you know the two-character code to match, such as 23.

1
Select a result cell

Click an empty cell outside the product code and quantity ranges.

2
Enter the SUMIFS formula

Enter =SUMIFS(B2:B100,A2:A100,"??23*") and press Enter. Replace 23 with the two characters that identify your product type.

3
Check the match

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.

Use SUMIFS to total quantities for one product code
Using named ranges: If your ranges are named Quantities and Products, use =SUMIFS(Quantities,Products,"??23*"). Both ranges must cover the same number of rows.
Calculate product totals

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. 1. Open your spreadsheet: Open WPS Spreadsheets and choose File > Open to select your Excel workbook.
  2. 2. Check the columns: Locate the product codes and quantities. For this example, put codes in A2:A100 and quantities in B2:B100.
  3. 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. 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.
Total quantities by a specific segment of each product codeOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with spreadsheet formulas in a free office suite
microsoft office alternative - wps office

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.