Someone asked a human this. So we asked the AI.
How to Use SUMIF in Excel (With Examples)
Somebody needed a function that adds only some numbers, and the function has been sitting there since forever.
The answer
SUMIF adds up the cells in a range that meet one condition. The syntax is =SUMIF(range, criteria, [sum_range]).
range: the cells to test against the condition.criteria: the condition, such as"North",">100"or a cell reference.sum_range: the cells to add. If you leave it out, Excel adds the cells inrangeitself.
Steps
- Click the cell where you want the total.
- Type
=SUMIF(. - Select the range to test, for example
A2:A7, then type a comma. - Type the criteria in quotes, for example
"North", then a comma. - Select the cells to add, for example
C2:C7, close the bracket and pressEnter.
The result is =SUMIF(A2:A7,"North",C2:C7).
Criteria forms
- Text match:
"North"(not case sensitive). - Number comparison:
">100","<=50","<>0". The operator goes inside the quotes. - Comparison against a cell:
">"&E1. - Wildcards:
"N*"(starts with N),"*pen*"(contains pen),"?????"(exactly five characters).
Multiple criteria (AND)
Use SUMIFS, where the sum range comes first: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2). Every condition must be true.
Multiple criteria (OR)
There is no OR argument in SUMIF or SUMIFS. Use one of these:
- OR in the same column:
=SUMPRODUCT(SUMIF(A2:A7,{"North","South"},C2:C7)). This adds the North total and the South total, and works in both Excel and Google Sheets. - OR across different columns: add the two conditions, then subtract the rows that match both so they are not counted twice:
=SUMIFS(C2:C7,A2:A7,"North")+SUMIFS(C2:C7,B2:B7,"Pen")-SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Pen"). A single-formula alternative is=SUMPRODUCT(C2:C7*(((A2:A7="North")+(B2:B7="Pen"))>0)). - OR combined with AND:
=SUMPRODUCT(SUMIFS(C2:C7,A2:A7,{"North","South"},C2:C7,">100"))adds sales over 100 for North or South.
The SUMPRODUCT wrapper makes the array of results add up without needing special entry. In Google Sheets, ARRAYFORMULA can be used instead, but SUMPRODUCT works in both.
Example
Data in A1:C7 (headers in row 1):
Region Product Sales
North Pen 120
South Pen 80
North Book 200
East Pen 50
South Book 150
North Pen 30=SUMIF(A2:A7,"North",C2:C7)returns350(120 + 200 + 30).=SUMIF(C2:C7,">100")returns470(120 + 200 + 150).=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Pen")returns150(120 + 30).=SUMPRODUCT(SUMIF(A2:A7,{"North","South"},C2:C7))returns580(350 + 230).=SUMIFS(C2:C7,A2:A7,"North")+SUMIFS(C2:C7,B2:B7,"Pen")-SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Pen")returns480(North or Pen: 350 + 280 - 150).
Where people go wrong
Leaving the operator outside the quotes.
=SUMIF(C2:C7,>100)is an error; write">100", or">"&E1when using a cell.Numbers stored as text. Text in
sum_rangeis ignored, and text numbers in the criteria range do not match numeric comparisons such as">100". Convert them to real numbers (for example with Text to Columns or by multiplying by 1) before using the formula.Mixing up argument order. In
SUMIFthe sum range is last; inSUMIFSit is first. Copying one pattern into the other gives wrong totals or errors.Ranges of different sizes in
SUMIFS. All criteria ranges must be the same size as the sum range, otherwise you get#VALUE!. UseA2:A7withC2:C7, notA2:A7withC2:C9.
The prompt you could have used
I have an Excel sheet with Region in column A, Product in B and Sales in C, rows 2 to 100. Give me a formula that totals Sales where Region is North or South and Sales is over 100, and explain how it works.
Updated 2 November 2026. Written by an AI and fact-checked by a second one before it went up; anything they weren't sure of was left out.
Someone asked you something like this? Send them a link →
More questions nobody needed to ask
- Excel IF Formula With Multiple Conditions (With Examples)
- How to Fix the #N/A Error in Excel (Causes and Fixes)
- Percentage Change Between Two Numbers in Excel
- How to Concatenate Strings in Excel (With Examples)
- How to Delete a Git Branch (Local and Remote)
More Excel questions · All questions · Just Fucking Prompt It