The COUNTIF Function in Numbers Explained
Overview of the COUNTIF Function in Numbers
The COUNTIF function will count the cells within a group of selected cells that match the criteria you specify. To put it another way, if you want to count how many cells match a single condition, then the COUNTIF function will help with this.
What It Does
- The COUNTIF function in Numbers will return the count of cells that match a condition you specify.
- Like many IF-based functions, COUNTIF uses boolean logic (true or false). If the criteria specified within the function statement is true, COUNTIF will count that cell. If it's false, it won't include it.
- COUNTIF uses a single condition against the test array (the collection of cells you're counting). If you have more than one condition, you must use the COUNTIFS function.
Function Overview
Purpose: Returns the count of cells in a collection that satisfy a given condition
Result: A count of cells that match the condition
Arguments: test-array, condition
- test-array: The group of cells you're applying the condition to
- condition: The criteria to use on whether or not the function should count the cell. The condition is written as an expression. From this expression, it'll compare or test the values in the test-array with results determined as true or false. This means if your condition comes back as true for that cell, the function will count that cell. If false, it won't count that cell. Your criteria for the condition can be expressed in several ways
- comparison operators (=, <, >, <=, >=, <>)
- constants (values that don't change)
- references (a value from another cell)
- ampersand concatenation operator (combine the contents of conditions or cells)
- wildcards (?*~) or REGEX
Basic Examples of the COUNTIF Function in Numbers
Once you've identified which cells you want to apply your criteria to, you can express the condition in several ways.
Here are some examples:
- Comparison Operator: Count if less than 50; =COUNTIF(A1:A5,">50")
- Constant: Count if the cell equals exactly "captain"; =COUNTIF(A1:A5),"captain")
- Reference: Count if cell equals what's in cell B2; =COUNTIF(A1:A5),B2)
- Ampersand Concatenation Operator: count if cell is less than what is present in cell B2; =COUNTIF(A1:A5),"<"&B2)
- Wildcard: Count if cell contains "NCC" regardless of other characters in cell; =COUNTIF(A1:A5,"NCC*")
Basic COUNTIF examples (web: click on each image to zoom; email: pinch to zoom): Comparison Operator, Constant, Reference, Ampersand Concatenation Operator, and Wildcard
Criteria Examples
Here are more ways to express the condition argument in the COUNTIF function.
COUNTIF Criteria Examples | Expression |
Count if equal to 10 | “10” |
Count if greater than 100 | “>100" |
Count if less than or equal to 100 | “<=100” |
Count if equal to “Bob” | “bob” |
Count if not equal to “Bob” | “<>bob” |
Count if cell starts with “ka” no matter how many characters come after | “ka*” |
Count if cell contains “ka” no matter how many characters before or after | “*ka*” |
Count if cell starts with “ka" and only 1 character after | “ka?” |
Count if cell is equal to A1 | A1 |
Count if cell is less than A1 | “<“&A1 |
Count it cell is less than a specific date | “<2/1/2025” |
A few things to point out. Notice that most expressions in the second column are in quotes. The exception is when referencing another cell. This is even true when comparing to a date as shown in the last row of the table (date format in month, day, year as per my local regional settings).
Also notice the wildcard expressions in rows 6, 7, and 8. The position of the wildcard matters as the function will search before or after the matching criteria. For example, "ka?" will match "kat" but not "kate" as the ? looks for a single character after "ka". However, when using an asterisk, the same example will match both as an asterisk can look for any number of characters before or after depending on placement (i.e., "*ka", "ka*" or "*ka*").
COUNTIF Considerations and Limitations
These are some consideration and limitations when using the COUNTIF function.
Considerations
- The test-array can encompass any number of cells
- Avoid concatenation with the test-array. Concatenation is okay when it entering the condition
- If selecting multiple columns or rows, keep the number of cells within each consistent (i.e., don't select 5 cells in one column and 12 cells in another column. This will most likely result in inaccurate counts)
- Cells references are not put in quotes, but everything else usually is
Limitations
- COUNTIF can only use a single condition. If you have more than one condition, use the COUNTIFS function instead
- COUNTIF is not case sensitive. This means "enterprise", "ENTERPRISE", or "Enterprise" are all counted the same
I used to work in a grocery store when I was a teen. Every day my manager asked me, "Bill, tell me how many Carlo Rossi's [wine] we have out there?" meaning how many were on the shelf and, ultimately, how many would we need to bring out from the back. We had several types of Carlo Rossi wines like Chardonnay, Burgundy, and so forth. These were the big jugs of wine (4 liters/approx. 1 gallon) and they were located on the bottom shelf. I'd have to get down on my knees and bend over somewhat awkwardly so I could see the entire shelf. Invariably, the wine varieties were all mixed up from customers shuffling them around. Counting all the wines while also trying to avoid scraping myself on the sharp edges of the shelf, I'd count how many of each wine variety there were. I never looked forward to this part of my day. If only this were a spreadsheet task where I could use the COUNTIF function to count just the Merlots, etc. It'd make the task so much easier. 🙂🍷
Additional Function References
COUNTIF - Apple Support
COUNT
COUNTA
COUNTBLANK
COUNTIFS
SUMIF
Member discussion