|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

COUNTIF

COUNTIF

  • Last updated on

Description 

Use this function to count the number of cells that meet a single criteria. Supported in Chains.   

 

Syntax

COUNTIF(range,criterion)

 

Inputs

This function has the following arguments:

Name Required Valid input
range Yes The group of cells you want to count. range can contain numbers, arrays, or references that contain numbers. Blank and text values are ignored.
criterion Yes A single number, expression, cell reference, or text string that identifies which cells will be counted. For multiple criteria, use.

 

Examples

Sample data

  A B
1 Item Value
2 red 56
3 green 39
4 orange 15
5 red 92

Sample formulas

Use case Formula Explanation and Result
Count the number of cells in a provided range that match a provided value. =COUNTIF(A1:A5,"red")

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A1:A5: This is the range of cells to evaluate.
  • "red": This is the criterion. The formula counts cells in the range that exactly match this text.

For this data set this formula returns 2.
Count the number of cells in a provided range that do not match a provided value. =COUNTIF(A1:A5,"<>red")

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A1:A5: This is the range of cells to evaluate.
  • "<>red": This is the criterion. The "<>" operator means "not equal to". The formula counts cells in the range that do not contain the exact text "red".

For this data set this formula returns 3.
Count the number of cells in a provided range that match a value in a referenced cell. =COUNTIF(A1:B5,A5)

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A1:B5: This is the range of cells to evaluate.
  • A5: This is the criterion. The formula uses the value currently in cell A5 ("red") and counts how many cells in the range A1:B5 match it.

For this data set this formula returns 2.
Count the number of cells in a provided range that contain any text. =COUNTIF(A2:B5,"*")

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A2:B5: This is the range of cells to evaluate.
  • "*": This is the criterion. The asterisk (*) wildcard matches any sequence of text characters. The formula counts cells in the range that contain text (it does not count numbers or blank cells).

For this data set this formula returns 4.
Count the number of cells in a provided range that match a provided pattern. =COUNTIF(A2:A5,"????ge")

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A2:A5: This is the range of cells to evaluate.
  • "????ge": This is the criterion. The question mark (?) wildcard matches any single character. This pattern matches any text that is exactly 6 characters long and ends with "ge".

For this data set this formula returns 1.
Count the number of cells in a provided range that match a provided pattern. =COUNTIF(A2:B5,"*d")

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A2:B5: This is the range of cells to evaluate.
  • "*d": This is the criterion. The asterisk (*) wildcard matches any sequence of characters preceding the letter "d". This pattern matches any text that ends with the letter "d".

For this data set this formula returns 2.
Count the number of cells in a provided range that have a value less than the one in a referenced cell. =COUNTIF(A1:B5,"<"&B3)

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A1:B5: This is the range of cells to evaluate.
  • "<"&B3: This is the criterion. It combines the less than operator ("<") with the value from cell B3 (which is 39). The ampersand (&) concatenates the operator and the cell value into a single criterion string like "<39". The formula counts numeric cells in the range that are less than 39.

For this data set this formula returns 1.
Count the number of cells in a provided range that have a value greater than a provided value. =COUNTIF(A1:B5,">=24")

This formula works in the following manner:

  • COUNTIF(: This is the function itself.
  • A1:B5: This is the range of cells to evaluate.
  • ">=24": This is the criterion. When using a logical operator (like >=) with a number, the entire criterion must be enclosed in quotes. The formula counts numeric cells in the range that are greater than or equal to 24.

For this data set this formula returns 3.

 

Notes

  • Wildcards (?, *, ~) only work with text values. The formula =COUNTIF(A1:B5,"???") will only count cells that contain three alphabetical characters. Most alphabetical characters are supported.
    There is an exception: If there was a number in the middle of a word (say "or3nge", the formula =COUNTIF(A2:A5,"????ge") would include that word in its count.
  • The tilde (~) is an escape character to match literal wildcards. Example: To count a the number of cells containing literal question marks (?) you would use "~?" like this: =COUNTIF(A1:B5,"~?").

 

Tips

  • The best way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference.
  • To count numbers only, use the COUNT function.
  • To count numbers and text, use the COUNTA function.
  • To count using multiple conditions, use the COUNTIFS function.
  • To count empty cells, use the COUNTBLANK function.

 

Related functions

COUNT
COUNTA
COUNTBLANK
COUNTIF
COUNTIFS
ISBLANK
ISERROR
ISNA
ISNUMBER
VALUE

Was this article helpful?
0 out of 0 found this helpful
Description  Use this function to count the number of cells that meet a single criteria. Supported in Chains.      Sy...