Write a basic SUMIF

=SUMIF(A2:A100, "Apple", B2:B100)

Excel checks each cell in A2:A100 against “Apple” and adds the matching B values. If you leave out the third range, it sums the range it tested instead.

Use comparisons in the condition

=SUMIF(B2:B100, ">100")

The condition can be a comparison written as text, so this totals every value above 100. You can also use ">=", "<", or "<>0" for not-equal.

Add multiple conditions with SUMIFS

=SUMIFS(C:C, A:A, "Apple", B:B, ">100")

SUMIFS puts the sum range first, then pairs of range and condition. This totals column C only where A is “Apple” and B is above 100.

Common mistakes / tips

  • Argument order flips. In SUMIF the sum range is last; in SUMIFS it is first.
  • Quote your comparisons. Conditions like ">100" must be in double quotes.
  • Match range sizes. The test range and sum range should cover the same rows.

Frequently asked questions

What is the difference between SUMIF and SUMIFS?

SUMIF handles a single condition, while SUMIFS handles two or more. They also order their arguments differently, so do not mix them up.

Can SUMIF use a cell as the condition?

Yes. Reference a cell instead of typing the value: =SUMIF(A:A, D1, B:B) sums where column A equals whatever is in D1.

Conditional aggregation is even smoother in code. Go further with our free Pandas course.