How to use SUMIF and COUNTIF in Excel

Last updated: · By the DeskHowTo editorial team
Quick answer

=SUMIF(range, criteria, [sum_range]) adds numbers that match a condition. =COUNTIF(range, criteria) counts how many cells match.

Example: =SUMIF(A2:A100,"West",B2:B100) totals column B where column A is West. For several conditions use SUMIFS / COUNTIFS.

On this page

Applies to Excel for Microsoft 365, Excel 2016 and later, Excel for Mac and Excel for the web.

Write SUMIF and COUNTIF

SUMIF - total sales for one region

  1. Click the result cell.
  2. Type =SUMIF( then select the criteria range (e.g. regions in A2:A100), type a comma.
  3. Type the condition in quotes, for example "West", or click a cell that holds the condition, then a comma.
  4. Select the numbers to add (B2:B100), close with ) and press Enter.

COUNTIF - count how many times a value appears

  1. Type =COUNTIF(A2:A100,"West") to count West in the range.
  2. Use wildcards: =COUNTIF(A2:A100,"*inc*") counts cells containing inc.

Multiple criteria with SUMIFS / COUNTIFS

  1. SUMIFS puts the sum range first: =SUMIFS(C2:C100,A2:A100,"West",B2:B100,">=100").
  2. COUNTIFS: =COUNTIFS(A2:A100,"West",B2:B100,">=100").

Criteria tips

  • Numbers: ">10" or "<=5" (operators inside quotes).
  • Cell reference criteria: ">"&E1 joins the operator with the value in E1.
  • Blanks: =COUNTIF(A2:A100,"") counts empty cells.

Common errors

  • SUMIF sum_range should be the same height as the criteria range.
  • Text numbers may not sum - convert them.
  • Extra spaces break matches - use TRIM.

Frequently asked questions

SUMIF vs SUMIFS?

SUMIF handles one condition; SUMIFS handles many, and the sum range is the first argument.

Another sheet?

Yes: =SUMIF(Sheet2!A:A,"West",Sheet2!B:B). Prefer limited ranges.

Dates?

Yes - use a date value or a cell reference containing a date.

Sources checked: Microsoft Support. Verified October 2026.