Use SUMIF, SUMIFS, and SUMPRODUCT to Sum Excel Quantities by Criteria
Question details
The user wants to total quantities that match specified materials, part types, and delivery dates.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Summarize a table of delivery dates, parts, materials, and quantities using one or more criteria.
- Observed behavior
- The desired total includes only quantities from rows that meet the selected criteria.
Check that your table has consistent columns and that quantities are numeric. In the examples below, column A contains delivery dates, B contains parts, C contains materials, and D contains quantities, with data in rows 2–11.
Choose a Formula for One or Multiple Criteria
Use SUMIF for material alone, SUMIFS for all three fields, or SUMPRODUCT for more flexible array logic.
Enter a delivery date in F2, a part type in G2, and a material in H2. Use an actual Excel date in F2, not text that only looks like a date.
Select an empty result cell and enter =SUMIF($C$2:$C$11,H2,$D$2:$D$11). This adds quantities in column D where the material in column C matches H2.
Select an empty result cell and enter =SUMIFS($D$2:$D$11,$A$2:$A$11,F2,$B$2:$B$11,G2,$C$2:$C$11,H2). Only rows matching the date, part, and material are added.
For a two-criterion alternative, select an empty cell and enter =SUMPRODUCT(($B$2:$B$11=G2)*($C$2:$C$11=H2)*$D$2:$D$11). This sums quantities matching the part and material; add a date comparison if the date must also match.

Sum Quantities by Multiple Criteria in WPS Spreadsheets
Open your workbook in WPS Spreadsheets and use SUMIFS to total quantities for a selected delivery date, part, and material.
- 1. Open the workbook: Open WPS Spreadsheets and select your Excel workbook. Confirm that dates, parts, materials, and quantities occupy columns A through D.
- 2. Enter the criteria: Enter the delivery date in F2, the part type in G2, and the material in H2.
- 3. Add the formula: Select an empty cell and enter =SUMIFS($D$2:$D$11,$A$2:$A$11,F2,$B$2:$B$11,G2,$C$2:$C$11,H2), adjusting the ranges to fit your data.
- 4. Check the result: Press Enter and compare the total with the rows matching all three criteria. Use File > Save to save the workbook.

Frequently Asked Questions
When should I use SUMIF instead of SUMIFS?
Use SUMIF for one criterion, such as material: =SUMIF($C$2:$C$11,H2,$D$2:$D$11). Use SUMIFS when the total must also match other fields, such as part and delivery date.
Why is my date criterion not matching a row?
Check that both the table values in column A and the criterion in F2 are actual Excel dates. An exact date comparison may also exclude cells that contain a time; use date-range criteria if those times need to be included.
Can COUNTIFS add the quantities for matching rows?
No. COUNTIFS counts matching rows. Use SUMIFS with the quantity range as its first argument to add the quantities instead.
Does SUMIFS include duplicate matching rows?
Yes. SUMIFS adds the quantity from every row that meets all specified criteria, even when multiple rows have the same date, part, and material.




