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!
- 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.
- Range Size Consistency: Every additional range must have the same number of rows and columns as the first range.
- Range Types: You can use both contiguous (adjacent) and non-contiguous (separated) ranges.
- Empty Cell Criteria: If a criteria references an empty cell, COUNTIFS treats it as zero (0).
- 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)
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”)
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”)
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”)
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).
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”).
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.
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:
- Single Condition Only: COUNTIF can only handle one condition at a time. For multiple conditions, use the COUNTIFS function.
- Range Requirement: COUNTIF needs an actual range. You can’t use an array or modify values within the formula before applying criteria.
- Long Numbers Issue: COUNTIF doesn’t count numbers longer than 15 digits correctly.
- Case Insensitivity: COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.
- 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.