Function Problems

Use SUMIF, SUMIFS, and SUMPRODUCT to Sum Excel Quantities by Criteria

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

The user wants to total quantities that match specified materials, part types, and delivery dates.

Sum Excel Quantities by Multiple Criteria with SUMIF, SUMIFS, and SUMPRODUCT
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.
Before you start

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.

Solution 1Recommended

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.

1
Set up the criteria

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.

2
Sum by material alone

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.

3
Sum by all three criteria

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.

4
Use SUMPRODUCT for array logic

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.

Choose a Formula for One or Multiple Criteria
Keep ranges aligned: Adjust every range to cover the same rows in your table. SUMIFS adds every matching row, including duplicates; COUNTIFS counts matching rows instead of summing their quantities.
WPS Spreadsheets

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. 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. 2. Enter the criteria: Enter the delivery date in F2, the part type in G2, and the material in H2.
  3. 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. 4. Check the result: Press Enter and compare the total with the rows matching all three criteria. Use File > Save to save the workbook.
Use SUMIF, SUMIFS, and SUMPRODUCT to calculate conditional totalsOpen and edit Microsoft Excel XLSX and XLS filesWork in a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

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.