How To Use SUMIF And COUNTIF In Excel (2026)

SUMIF adds up the numbers in a range that meet a condition, and COUNTIF counts the cells that meet one.

Together they answer questions such as how much one category sold and how many entries match a word or a number.

This guide follows Microsoft’s support pages for both functions and shows the exact formulas, wildcards and mistakes to watch for.

Quick answer: the two formulas

FunctionSyntaxWhat it returns
SUMIF=SUMIF(range, criteria, [sum_range])The total of cells that meet the criteria
COUNTIF=COUNTIF(range, criteria)The number of cells that meet the criteria
SUMIFSMultiple criteria version of SUMIFTotal where several conditions are true
COUNTIFSMultiple criteria version of COUNTIFCount where several conditions are true

How SUMIF works

SUMIF checks each cell in the range against your criteria and adds the matching cells.

ArgumentRequiredMeaning
rangeYesThe cells Excel tests against the criteria
criteriaYesA number, expression, cell reference, text or function that defines which cells count
sum_rangeNoThe cells to add when they differ from the range; if left out, Excel adds the cells in range

Blank and text cells in the range are ignored, and dates in standard Excel format are allowed.

SUMIF examples from Microsoft

Microsoft’s example sheet lists property values in A2:A5 and commissions in B2:B5.

FormulaWhat it does
=SUMIF(A2:A5,”>160000″,B2:B5)Adds commissions for property values over 160,000
=SUMIF(A2:A5,”>160000″)Adds the property values themselves that are over 160,000
=SUMIF(A2:A5,300000,B2:B5)Adds commissions where the property value is exactly 300,000
=SUMIF(A2:A5,”>”&C2,B2:B5)Adds commissions where the value is greater than the number in C2

The last formula joins the operator and the cell with an ampersand, which is how you build a criterion from a cell.

SUMIF with text and blanks

A second Microsoft example uses categories in A2:A7, foods in B2:B7 and sales in C2:C7.

FormulaWhat it adds
=SUMIF(A2:A7,”Fruits”,C2:C7)Sales of every food in the Fruits category
=SUMIF(B2:B7,”*es”,C2:C7)Sales of foods whose names end in “es”
=SUMIF(A2:A7,””,C2:C7)Sales of foods that have no category

Text criteria and any criteria that contain a comparison symbol must sit inside double quotation marks, while a plain number does not need them.

How COUNTIF works

COUNTIF has only two arguments, the range to look in and the criteria to match.

It accepts a single criterion, so use COUNTIFS when you need more than one.

FormulaWhat it counts
=COUNTIF(A2:A5,”apples”)Cells that contain the word apples
=COUNTIF(A2:A5,A4)Cells that match the value in A4
=COUNTIF(B2:B5,”>55″)Cells with a value greater than 55
=COUNTIF(B2:B5,”<>”&B4)Cells whose value is not equal to the value in B4
=COUNTIF(A2:A5,”*”)Cells that contain any text
=COUNTIF(A2:A5,”?????es”)Cells with exactly seven characters that end in “es”

In Microsoft’s sample data, the apples formula returns 2 and the greater-than-55 formula returns 2.

Wildcards in criteria

SymbolMeaningExample
?Any single character“apple?” matches apple plus one more character
*Any run of characters“apple*” matches anything that starts with apple
~Treat the next ? or * as a literal character“*~?” finds text ending in a question mark

Wildcards work with text, so they do not help you match numbers.

Comparison operators in criteria

Put the operator and the number together inside quotation marks, as in “>32”.

To compare with the value in a cell, close the quotation marks and join the cell with an ampersand, as in “>”&C2.

Matching is not case-sensitive, so “apples” and “APPLES” are counted as the same.

Use SUMIFS or COUNTIFS for more than one condition

SUMIFS adds values that meet several conditions at once, and COUNTIFS counts the cells where every condition is met.

FunctionSyntaxLimit
SUMIFS=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Up to 127 range and criteria pairs
COUNTIFS=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)Up to 127 range and criteria pairs

The argument order is the opposite of SUMIF, because in SUMIFS the sum_range comes first instead of third.

Microsoft calls mixing up the two orders a common error, so check it whenever you copy or edit a formula.

Every criteria_range in SUMIFS must have the same number of rows and columns as the sum_range.

In Microsoft’s sample sheet, quantities are in A2:A9, products in B2:B9 and salespeople in C2:C9.

FormulaResultMeaning
=SUMIFS(A2:A9, B2:B9, “=A*”, C2:C9, “Tom”)20Products starting with A that Tom sold
=SUMIFS(A2:A9, B2:B9, “<>Bananas”, C2:C9, “Tom”)30Everything Tom sold except bananas

In a SUMIFS sum_range, TRUE counts as 1 and FALSE counts as 0, which can give an unexpected total.

Other ways to count cells

Microsoft’s counting page lists several related tools besides COUNTIF.

ToolWhat it counts
COUNTCells that contain numbers
COUNTACells that contain any value, so blanks are skipped
COUNTBLANKBlank cells in a contiguous range, including cells that return empty text
Status barCount, Numerical Count, Sum, Average, Min and Max for selected cells

Right-click the status bar to show or hide any of those values.

COUNT skips a time that includes text such as a.m., which is why a count can come out lower than you expect.

If the result looks wrong

ProblemCauseFix
Wrong count or total for a long text stringThe functions cannot correctly match strings longer than 255 charactersShorten the text, or for COUNTIF join pieces with & or CONCATENATE
No result when you expected oneThe criteria is not in quotation marksWrap text and operator criteria in double quotes
#VALUE! error from COUNTIFThe formula refers to another workbook that is closedOpen the other workbook
Unexpected countsExtra spaces, mixed straight and curly quotes or hidden charactersClean the data with TRIM or CLEAN

If your formulas show up as text instead of results, see how to fix Excel formulas not calculating.

Keep sum_range the same size as range

Microsoft recommends that sum_range has the same size and shape as range.

If it does not, the formula sums a block that starts at the first cell of sum_range but has the same dimensions as range.

For example, a range of A1:A5 with a sum_range of B1:K5 still adds only B1:B5.

What COUNTIF cannot do

COUNTIF cannot count cells by background color or font color.

Microsoft says a VBA user-defined function is needed for that.

Use named ranges for readability

A named range lets you write a formula such as =COUNTIF(fruit,”>=32″) instead of a cell address.

Names work on the same sheet, on another sheet in the workbook or in another workbook that is open.

Official sources

Leave a Reply

Your email address will not be published. Required fields are marked *