How to Use the COUNTIF Function in Excel: A Comprehensive Guide

Angelina Robinson

How to Use the COUNTIF Function in Excel

Excel’s COUNTIF and COUNTIFS functions are powerful tools for counting cells that meet specific criteria.

They work with numbers, text, and dates, helping you quickly summarize your data.

This guide will show you how to use these functions, from basic counts to more complex scenarios with multiple criteria and wildcards.

Excel COUNTIFS – Things to Remember!

  1. Single and Multiple Conditions: The COUNTIFS function can count cells in a single range with one condition or in multiple ranges with multiple conditions. Only cells meeting all conditions are counted.
  2. Range Size Consistency: Every additional range must have the same number of rows and columns as the first range.
  3. Range Types: You can use both contiguous (adjacent) and non-contiguous (separated) ranges.
  4. Empty Cell Criteria: If a criteria references an empty cell, COUNTIFS treats it as zero (0).
  5. Wildcard Characters: Use an asterisk (*) to represent any number of characters and a question mark (?) to represent a single character in your criteria.

Must Read: How to Wrap Text in Excel – A Simple Guide By an Expert

How to Use the COUNTIF Function in Excel

The COUNTIF function in Excel helps you count cells that meet a specific condition. You can use it directly in your cell formulas. The usage is the same in both Excel and VBA.

Let’s look at some examples to understand how the COUNTIF function works.

1. Count Cells with a Specific Value

Suppose you have a list of numbers in cells A2 to A7, and you want to count how many times the number 33 appears.

Formula:

=COUNTIF(A2:A7, 33)

Count Values with the Given Value

This formula checks the cells A2 to A7 for the number 33. If only one cell contains 33, the result will be 1, which will be displayed in cell A8.

2. Count Cells with Values Less Than a Given Number

Suppose you have data in cells A12 to A17 and want to count how many values are less than 50.

Formula:

=COUNTIF(A12:A17, “<50”)

Count Numbers with a Value Less Than the Given Number

This formula checks the cells A12 to A17 for values less than 50. If four cells contain values less than 50, the result will be 4, which will be displayed in cell A18.

3. Count Cells with a Specific Text Value

Suppose you have a list of names in cells A22 to A27 and want to count how many times the name “john” appears.

Formula:

=COUNTIF(A22:A27, “john”)

Count Values with the Given Text Value

This formula checks the cells A22 to A27 for the text “john”. If one cell contains “john”, the result will be 1, which will be displayed in cell A28.

4. Count Negative Numbers

Suppose you have data in cells A32 to A37 and want to count how many numbers are negative.

Formula:

=COUNTIF(A32:A37, “<0”)

Count Negative Numbers

This formula checks the cells A32 to A37 for negative numbers (less than 0). If three cells contain negative numbers, the result will be 3, which will be displayed in cell A38.

5. Count Cells with Zero Values

Suppose you have data in cells A42 to A47 and want to count how many times the number 0 appears.

Formula:

=COUNTIF(A42:A47, 0).

Count Zero Values

This formula checks the cells A42 to A47 for the number 0. If two cells contain 0, the result will be 2, which will be displayed in cell A48.

Must Read: How To Lock Cells In Excel When Scrolling – Simple & Easy Steps

How To Use The Countif Function In Excel With Multiple Criteria

Counting cells with multiple criteria in Excel is easy using the COUNTIFS function. This function counts cells where all specified conditions are true, working similarly to Excel’s AND function.

Example 1: COUNTIFS with Multiple Criteria

Imagine you have a product list as shown below. You want to count items that are in stock (column B value > 0) but not sold yet (column C value = 0).

Use this formula:

=COUNTIFS(B2:B7, “>0”, C2:C7, “=0”)

The count is 2 (for “Cherries” and “Lemons”).

COUNTIFS formula with multiple criteria

Example 2: COUNTIFS with Identical Criteria

If you want to count items with the same criteria, you need to specify each criteria_range and criteria pair separately.

To count items with 0 in both column B and column C, use this formula:

=COUNTIFS($B$2:$B$7, “=0”, $C$2:$C$7, “=0”)

This formula returns 1 because only “Grapes” have 0 in both columns.

COUNTIFS formula with two criteria

Using a simpler formula like =COUNTIFS(B2:C7, “=0”) would give a different result – the total count of cells in the range B2

How to use COUNTIFS with Wildcard Characters

Using the COUNTIFS function with wildcard characters in Excel allows you to count cells based on partial matches. Wildcards can be very useful when you want to count cells that contain specific characters or patterns.

Wildcard Characters in Excel

  • * (asterisk) – Represents any number of characters. For example, “ap*” will match “apple”, “apricot”, etc.
  • ? (question mark) – Represents any single character. For example, “b?g” will match “bag”, “big”, “bug”, etc.

Example 1: Using Asterisk (*) in COUNTIFS

Suppose you have a list of product names and you want to count how many products start with the letter “A”.

Your formula would look like this:

=COUNTIFS(A2:A10, "A*")

This formula counts all cells in the range A2

that start with “A”.

Example 2: Using Question Mark (?) in COUNTIFS

Now, let’s say you want to count how many products have exactly 5 characters in their names and the third character is “e”.

Your formula would be:

=COUNTIFS(A2:A10, "??e??")

This formula counts all cells in the range A2

that have exactly five characters and “e” as the third character.

Combining Wildcards with Other Criteria

You can also combine wildcards with other criteria. For example, if you want to count products that start with “A” and have more than 10 items in stock (column B > 10), you can use:

=COUNTIFS(A2:A10, "A*", B2:B10, ">10")

This formula counts all cells in the range A2

that start with “A” and have corresponding values in the range B2

greater than 10.

Must Read: How To Create Drop Down List In Excel With Multiple Selections

Tips for Using Wildcards

  • Make sure your criteria strings are enclosed in double quotes.
  • Use the * wildcard for flexible matching and ? for specific single character positions.
  • Combine multiple criteria to refine your counts as needed.

Wildcards make the COUNTIFS function powerful and flexible, helping you count cells based on various patterns and conditions.

Limitations of the COUNTIF Function

The COUNTIF function has some limitations:

  1. Single Condition Only: COUNTIF can only handle one condition at a time. For multiple conditions, use the COUNTIFS function.
  2. Range Requirement: COUNTIF needs an actual range. You can’t use an array or modify values within the formula before applying criteria.
  3. Long Numbers Issue: COUNTIF doesn’t count numbers longer than 15 digits correctly.
  4. Case Insensitivity: COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.
  5. Other Quirks: There are additional quirks, which are explained in this article.

To overcome these limitations, you can use the SUMPRODUCT function. In the latest version of Excel, you can also use the BYROW and BYCOL functions.

Wrap Up

The COUNTIF and COUNTIFS functions in Excel are versatile for counting cells that meet various conditions.

COUNTIF is useful for simple counts with one criterion, while COUNTIFS is great for more complex analysis with multiple criteria.

Learning these functions, including the use of wildcards, can greatly improve your data analysis in Excel. Be aware of their limitations and consider other functions like SUMPRODUCT for advanced scenarios.

FAQs

What is the difference between COUNTIF and COUNTIFS?

COUNTIF counts cells based on a single criterion, while COUNTIFS can count cells based on multiple criteria.

Can COUNTIF and COUNTIFS be used with text and dates?

Yes, both functions can count cells with numbers, text, and dates.

What are wildcards, and how do they work with COUNTIF and COUNTIFS?

Wildcards are special characters like * (any sequence of characters) and ? (any single character) that help match patterns in text. They can be used in COUNTIF and COUNTIFS to count cells that match specific patterns.

Are there any limitations to using COUNTIF and COUNTIFS?

Yes, these functions may not handle very complex criteria well. For advanced scenarios, consider using alternative functions like SUMPRODUCT.

How can mastering COUNTIF and COUNTIFS improve my data analysis skills?

By learning these functions, you can efficiently count and analyze data that meets specific conditions, making your data analysis faster and more accurate.

About the author

Hey, it's Angelina Robinson! If you're confused by Excel, don't worry, I've got your back. I've spent years mastering it, and I want to help you make the most of it.

I got into Excel because I was fascinated by everything it can do. Now, I help people and companies use it better for their work.

So, my blogging story started when I met my friend Angelina Robinson. We hit it off and decided to team up. Now, in our 50s, we've made TopExcelTips.com to share what we know with the world. My thing? Making tricky topics simple and exciting.

Leave a Comment