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
| Function | Syntax | What 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 |
| SUMIFS | Multiple criteria version of SUMIF | Total where several conditions are true |
| COUNTIFS | Multiple criteria version of COUNTIF | Count where several conditions are true |
How SUMIF works
SUMIF checks each cell in the range against your criteria and adds the matching cells.
| Argument | Required | Meaning |
|---|---|---|
| range | Yes | The cells Excel tests against the criteria |
| criteria | Yes | A number, expression, cell reference, text or function that defines which cells count |
| sum_range | No | The 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.
| Formula | What 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.
| Formula | What 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.
| Formula | What 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
| Symbol | Meaning | Example |
|---|---|---|
| ? | 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.
| Function | Syntax | Limit |
|---|---|---|
| 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.
| Formula | Result | Meaning |
|---|---|---|
| =SUMIFS(A2:A9, B2:B9, “=A*”, C2:C9, “Tom”) | 20 | Products starting with A that Tom sold |
| =SUMIFS(A2:A9, B2:B9, “<>Bananas”, C2:C9, “Tom”) | 30 | Everything 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.
| Tool | What it counts |
|---|---|
| COUNT | Cells that contain numbers |
| COUNTA | Cells that contain any value, so blanks are skipped |
| COUNTBLANK | Blank cells in a contiguous range, including cells that return empty text |
| Status bar | Count, 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
| Problem | Cause | Fix |
|---|---|---|
| Wrong count or total for a long text string | The functions cannot correctly match strings longer than 255 characters | Shorten the text, or for COUNTIF join pieces with & or CONCATENATE |
| No result when you expected one | The criteria is not in quotation marks | Wrap text and operator criteria in double quotes |
| #VALUE! error from COUNTIF | The formula refers to another workbook that is closed | Open the other workbook |
| Unexpected counts | Extra spaces, mixed straight and curly quotes or hidden characters | Clean 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.
Related Excel guides
- Use XLOOKUP and VLOOKUP in Excel for pulling a single value from a table.
- Create a pivot table in Excel to total and count by category without formulas.
- Convert numbers stored as text when a criterion on numbers finds nothing.
Official sources
- SUMIF function (Microsoft Support)
- Use the COUNTIF function in Microsoft Excel (Microsoft Support)
- SUMIFS function (Microsoft Support)
- Ways to count values in a worksheet (Microsoft Support)