SUMIFS adds values from rows that meet every condition you specify. Use it for totals such as sales of one product in one region, or transactions within a date range. SUMIF handles a single condition; SUMIFS puts the sum range first and then uses range/criteria pairs.
SUMIFS works in Excel for Microsoft 365, Excel for the web, Excel 2024, Excel 2021, Excel 2019 and Excel 2016, including supported Mac editions. See Microsoft’s SUMIFS documentation.
Enter these headers in A1:D1 and the six rows in A2:D7 on a blank worksheet. Enter the dates as real Excel dates, using =DATE(2026,1,1) for the first date if needed, rather than storing date labels as text.
A: Product
B: Region
C: Date
D: Sales
Chips2
North
1 Jan 2026
100
Chips3
North
3 Jan 2026
150
Biscuits2
North
6 Jan 2026
80
Chips2
South
4 Jan 2026
120
Lays Chips
North
31 Jan 2026
90
Chips2
North
1 Feb 2026
200
=SUMIFS($D$2:$D$7,$A$2:$A$7,"Chips2",$B$2:$B$7,"North") returns 300 (100 + 200). Both conditions must be true in the same row. The South sale is excluded.
Numeric Criteria and Cell References
Put 100 in F2. =SUMIFS($D$2:$D$7,$B$2:$B$7,"North",$D$2:$D$7,">"&F2) returns 350 (150 + 200). Join the quoted comparison operator to a cell reference with &. For an exact cell-based condition, pass the cell reference directly.
SUMIFS Between Two Dates
=SUMIFS($D$2:$D$7,$C$2:$C$7,">="&DATE(2026,1,1),$C$2:$C$7,"<"&DATE(2026,2,1)) returns 540. This includes January 1 through January 31 and excludes February 1. Using an exclusive upper boundary also includes times during the final day when the source contains timestamps.
Wildcards and OR Conditions
=SUMIFS($D$2:$D$7,$A$2:$A$7,"Chips?",$B$2:$B$7,"North") returns 450. The question mark matches one character, so Chips2 and Chips3 qualify. =SUMIFS($D$2:$D$7,$A$2:$A$7,"*Chips") returns 90 for Lays Chips. Keep criteria free of unintended trailing spaces; use ~ before * or ? to match either character literally.
For Chips2 or Biscuits2, use =SUMIFS($D$2:$D$7,$A$2:$A$7,"Chips2")+SUMIFS($D$2:$D$7,$A$2:$A$7,"Biscuits2"). The result is 500 (420 + 80). These two product conditions cannot overlap. Adding separate SUMIFS results for overlapping conditions can count a row twice; the OR function alone does not turn SUMIFS criteria pairs into OR logic.
Why SUMIFS Returns Zero or #VALUE!
All criteria ranges must have the same dimensions as the sum range. For example, =SUMIFS(D2:D7,A2:A6,"Chips2") returns #VALUE! because one range has six rows and the other has five. If the result is zero, check the text, spaces, dates and criteria operators. Numbers stored as text in the sum range are not added as numeric values. Use straight quotation marks when entering formulas; some regional settings require semicolons instead of commas.
Step-by-step Guide to Use SUMIF for One Condition
Open your Excel spreadsheet and select the cell where you want the sum to appear.
Type =SUMIF( in the cell you selected.
Select the range of cells that you want to apply the criteria to.
Type a comma, then type the criteria that you want to apply. This can be a number, text, or a logical expression.
Type a comma, then select the range of cells that you want to sum.
Type a closing parenthesis “)” and press Enter.
Examples
Numerical Criteria
Summing values using a number criteria. In the above example, we have a list of numbers, and we want to sum up the numbers that are greater than or equal to 4. To do this, we can use the SUMIF function. First, we input the formula =SUMIF(C2:C9, “>=4”) and press Enter. This tells Excel to add up all the numbers of cells in the range C2:C9 that are greater than or equal to 4.
Adding multiple criteria. In the same example, we want to add up the numbers “Product Code 3” in Office 1 and “Product Code 6” in Office 2. To do this, we can use the separate SUMIF formulas added together. We begin by entering the formula =SUMIF(A2:A9,3,B2:B9)+SUMIF(A2:A9,6,C2:C9) and pressing Enter. This tells Excel to add up all the numbers of cells in the range B2:B9 that have a product code of 3, and the numbers of cells in the range C2:C9 that have a product code of 6.
Text Criteria
Summing values using a text criteria. In the following example, we have a list of products in column E and the amount of sale in column F, and we want to add the number of sales that are by “Brit Biscuits.” To do this, we can use the SUMIF function. First, we input the formula =SUMIF(E2:E9, “Brit Biscuits”, F2:F9) and press Enter. This tells Excel to add all values in F2:F9 that are by Brit Biscuits.
Summing values excluding an item. Using the same example, we want to add up all the sales of all products EXCEPT for “Brit Biscuits.” To do this, we can take the same formula from before and simply add a non-equal-to (<>) operator before the criteria and press enter. The formula should look like this: =SUMIF(E2:E9, “<>Brit Biscuits”, F2:F9). This tells Excel to add all values in F2:F9 that are NOT by Brit Biscuits.
Summing values that start with the same text. In the same example, we have different products that have “chips” and “biscuits” in their names. We want to find out how many Chips2 and Chips3 have sold. To do this, since we know that both products start with the word “Chips,” and only have one character after them, we can add a question mark (?) after the word. The question mark indicates a wildcard character for an individual character, which in this case is the 2 and 3.
Use =SUMIF(E2:E9,"Chips?",F2:F9) to add sales for names such as Chips2 and Chips3. Chips? matches exactly one character after Chips; it does not match Chips20.
Summing values that end with the same text.Similarly, we want to find out how many Lays Chips and Uncle Chips have sold. To do this, since we know that both products contain “Chips” in their names, we can use that to set the criteria. In the formula, simply add an asterisk (*) before the “chips.” The asterisk indicates a wildcard character for any character, which in this case is “Lays” and “Chips.”
Use =SUMIF(E2:E9,"*Chips",F2:F9) to add sales for names ending in Chips, such as Lays Chips and Uncle Chips.
Adding multiple criteria. In the same example, we want to add up the sales of Chips2, Chip3, Biscuits2, and Biscuits3. To do this, we can use the separate SUMIF formulas added together, while also using the question mark. We begin by entering the formula =SUMIF(E2:E9, “Chips?”, F2:F9)+SUMIF(E2:E9, “Biscuits?”, F2:F9) and pressing Enter. This tells Excel to add all values in F2:F9 that start with “biscuits” and “chips.”
Date Criteria
Summing values using a date criteria. In the following example, we have dates of transactions from April 1, 2021 to April 7, 2021 in column H and the amount in column I, and we want to add the number of transactions after April 3. To do this, we can use the SUMIF function. First, we input the formula =SUMIF(H2:H8, “>”&DATE(2021,04,3), I2:I8) and press Enter. This tells Excel to add all values in I2:I8 after the 3rd of April.
Summing values using today’s date as a criteria. Using the same example, let’s say today’s date is April 7, 2021, and we want to add the number of transactions after today. To do this, we can use the SUMIF function. First, we input the formula =SUMIF(H2:H11, “>”&TODAY(), I2:I11) and press Enter. This tells Excel to add all values in I2:I11 after today.
Summing values between dates. To include April 3 through April 6 in the existing H/I example, use =SUMIFS(I2:I11,H2:H11,">="&DATE(2021,4,3),H2:H11,"<"&DATE(2021,4,7)). The lower boundary includes April 3; the upper boundary excludes April 7, so April 6 timestamps are included.
Multiple Criteria
In the following example, we have the type of product in column H, the company in column I, and the price in column J. We want to find out the total price of chips by the company, Lays. To do this, we use the SUMIFS function. First, we input the formula =SUMIFS(J14:J22,H14:H22,”Chips”,I14:I22,”Lays”) and press Enter. This tells Excel to add all values in J14:J22 that are Chips AND also by Lays.
Conclusion
Use SUMIF for one condition and SUMIFS for several conditions that must all be met. Keep the ranges aligned, put comparison operators in quotes, and use real date values for date criteria.
