Excel COUNTIF and SUMIF

COUNTIF and SUMIF let you count or add up cells that meet a single condition, forming the basis of quick conditional reporting in Excel.

Counting Cells That Meet a Condition: COUNTIF

COUNTIF looks through a range and counts how many cells satisfy a criteria you specify. Its syntax is COUNTIF(range, criteria), where range is the cells to check and criteria is the condition, written as a number, text in quotes, or a comparison.

Example

=COUNTIF(A:A,">10")

Adding Values That Meet a Condition: SUMIF

SUMIF works the same way but returns a total instead of a count. Its syntax is SUMIF(range, criteria, [sum_range]): range is checked against criteria, and the matching amounts are pulled from sum_range, which can be a different set of cells than range itself.

Example

=SUMIF(A:A,"Yes",B:B)

In that example, Excel checks every cell in column A for the text "Yes", and for every row where it finds a match, it adds the corresponding value from column B into the total.

FunctionPatternReturns
COUNTIFCOUNTIF(range, criteria)A count of matching cells.
SUMIFSUMIF(range, criteria, sum_range)A total of matching values.
COUNTIFSCOUNTIFS(range1, criteria1, range2, criteria2, ...)A count where every condition is true at once.
SUMIFSSUMIFS(sum_range, range1, criteria1, range2, criteria2, ...)A total where every condition is true at once.

Writing Criteria Correctly

  • "Yes" matches the exact text Yes.
  • ">100" matches any value greater than 100; comparison operators must be inside the quotes.
  • "<>0" matches anything that is not zero.
  • "A*" matches any text starting with the letter A, using * as a wildcard for any number of characters.
  • ">"&C1 builds a dynamic criteria from the value in C1 by joining text and a cell reference with &.
Note: When a condition depends on more than one column at once, such as "Region is East AND Status is Closed", switch to COUNTIFS or SUMIFS, which accept any number of range-criteria pairs and require all of them to match.

Keep Going

Excel Interview QuestionsPractice real questions asked for ExcelData Analyst SalarySee what Data Analyst roles pay in the marketAuto Apply for JobsApply to open roles related to Excel and tweak your resume