Excel COUNTIF & COUNTIFS Functions: How to Use & Examples

Counting cells in Excel is very easy. In fact, simply selecting the cells will automatically show the count in the status bar. However, what if you need to count the frequency with which a specific text or numerical value appears in those cells? What if you need to count based on multiple criteria applied to different ranges? Fortunately, Microsoft Excel’s COUNTIF and COUNTIFS functions allow you to count cells based on whichever and however many criteria you need.

In this guide, you will review the syntax of both COUNTIF and COUNTIFS in Microsoft Excel and some must-know operators you can use to write criteria to match text, numbers, blank & non-blank cells, and Boolean values. You have step-by-step instructions on using COUNTIF to match exact and partial text and match numbers greater than or equal to a specific value.

While it's possible to use multiple criteria with COUNTIF, there's no need because that's why Excel has COUNTIFS. You also have examples with step-by-step instructions on how to use COUNTIFS with multiple criteria applied to one or more ranges.

COUNTIF Function Syntax in Excel

First, let’s review the syntax for both functions: COUNTIF and COUNTIFS.

COUNTIF Syntax

The COUNTIF syntax in Excel has two required parameters.

=COUNTIF(range, criteria)

• range: the cells you want to count. These can be cell references to arrays or named ranges
• criteria: the condition that determines whether to count specific cells. This can be an expression, a number, a string, or a cell reference.

COUNTIFS Syntax

Like COUNTIF, the COUNTIFS function has two required parameters. However, you can provide additional parameters to test multiple ranges using different criteria. Remember that all ranges must have the same dimensions, meaning the same number of rows and columns.

=COUNTIFS(criteria_range1, criterion1, criteria_range2*, criterion2*,…)

• criteria_range1: the range of cells you want to test using the first criterion.
• criterion1: the condition or test that you want to apply to the first range.

• criteria_range2*: the range of cells you want to test using the second criterion.
• criterion2*: the condition or test that you want to apply to the second range.

How to Write COUNTIF Criteria or Conditions?

Below, you have some of the most useful operators you can use in your COUNTIF criteria, whether you need to count based on text, numbers, blanks & non-blanks, or Boolean values.

Text Values

When it comes to matching text values, you have a lot of options. For an exact match, just enclose the string in quotation marks or the reference to the cell containing the string. For partial matches, you can use the wildcard characters below.

• * Matches 0 or more characters.
• ? Matches a single character.
• ~ If you need to search for the symbols “*” or “?”, add the tilde before the symbol to escape it, so it's not used as a wildcard.

Finally, the function is case insensitive, so it won’t distinguish between uppercase and lowercase.

Numeric Values

If you want to use an exact numeric value as the criterion, you don’t need to enclose it in quotation marks. However, if you want to use comparison operators, you need to enclose the criterion in quotation marks.

• <> not equal
• = equal
• >= greater than or equal
• <= less than or equal
• > greater than
• < less than

Blank & Non-Blank Cells

You can use the following criteria to search for blank and non-blank cells, respectively.

=COUNTIF(range, “ ”)

=COUNTIF(range, “<>”)

Boolean Values

Finally, you can also use the Boolean operators TRUE and FALSE to count cells based on the results of a formula or to count checked or unchecked checkboxes.

Examples of How to Use COUNTIF in Microsoft Excel

Now that you have reviewed the syntax for the COUNTIF function, it’s time to see some examples of how you can use it in different situations.

Imagine you have an Excel table with sales data, which includes the name of the sales agent, their team, the product, and the sales amount. You need to get different counts for your sales report, but counting manually would be a nightmare, as the table has five hundred rows.

First, select each column and go to the ‘Formulas’ tab. Click ‘Create from Selection’ to name the ranges so it’s easier to work with the data.

COUNTIF Cell Contains Exact Text

Follow the steps below to count cells that contain specific text. In this case, let's count the number of sales by ‘Barret Smitten’.

1. 1. Type the COUNTIF function and select the range of cells you want to count. In this case, I will use the named range ‘sales_agent’.

1. 2. Add a semicolon and type the criterion between quotation marks.

1. 3. Add the closing parenthesis and press ‘Enter’ to see the result.

COUNTIF Cell Contains Partial Text

Instead of using an exact match, you can use wildcards to search for part of the text. For example, let’s count the number of sales made by ‘Northteam’ by matching ‘North’.

1. 1. Type the COUNTIF function and select the range of cells you want to count.

1. 2. Add a semicolon and type the criterion between quotation marks.

1. 3. Add the closing parenthesis and press ‘Enter’ to see the result.

COUNTIF Value Greater Than or Equal To

For this example, let’s find the number of sales for an amount greater than or equal to $500.

1. 1. Type the COUNTIF function and select the range of cells you want to count.

1. 2. Add a semicolon and type the criterion between quotation marks.

1. 3. Add the closing parenthesis and press ‘Enter’ to see the result.

Examples of How to Use COUNTIFS in Microsoft Excel

In Microsoft Excel, you can use the COUNTIFS function to count cells based on multiple criteria, and each criterion can be applied to one or more ranges.

COUNTIFS with Multiple Criteria for a Single Range

For example, let’s find the number of sales for amounts between $500 and $1000.

1. 1. Type the COUNTIFS function and select the range of cells you want to count.

1. 2. Add a semicolon and type the first criterion between quotation marks.

1. 3. Add a semicolon and select the same range as before. Add another semicolon and type the second criterion between quotation marks.

1. 4. Add the closing parenthesis and press ‘Enter’ to see the result.

COUNTIFS with Different Criteria for Different Ranges

Finally, let’s count the number of sales of productA for an amount greater than or equal to $700.

1. 1. Type the COUNTIFS function and select the first range of cells.

1. 2. Add a semicolon and type the first criterion between quotation marks.

1. 3. Add a semicolon and select the second range of cells. Add another semicolon and type the second criterion between quotation marks.

1. 4. Add the closing parenthesis and press ‘Enter’ to see the result.

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

COUNTIF and COUNTIFS are very useful functions in Microsoft Excel. You can count cells based on their contents by specifying a single criterion or multiple criteria. These criteria can be applied to a single range or multiple ranges and can be as simple or complex as you want. You can use logical operators, Boolean values, and wildcards to define your criteria.

Additionally, you can use named ranges for clearer formulas and less typing. You now know how to use COUNTIF to count cells containing numeric values, as well as text values using both exact and partial matches. Additionally, you know how to use COUNTIFS to apply multiple criteria to a single range or to multiple ranges.

To learn more about Excel formulas and how to use COUNTIF and SUMIF in Google Sheets, check out the guides below.

• COUNTIF & COUNTIFS in Google Sheets: Formula & Examples
• SUMIF & SUMIFS in Google Sheets: Formula & Examples
• 75 Must-Know Excel Formulas (+ Examples)