COUNT, COUNTIF, COUNTIFS

Count function

Summary 

The Excel COUNT function returns the count of values that are numbers, generally cells that contain numbers. Values can be supplied as constants, cell references, or ranges.

Purpose

Count numbers

Return value 

Count of numeric values

Syntax 

=COUNT (value1, [value2], ...)

Arguments 

value1 - An item, cell reference, or range.

value2 - [optional] An item, cell reference, or range.

Usage notes 

The COUNT function returns the count of numeric values in the list of supplied arguments. Arguments can be individual items, cell references, or ranges up to a total of 255 arguments. All numbers including negative values, percentages, dates, fractions, and time are counted. Empty cells and text values are ignored.

Example #1 - range

In the example shown, COUNT is set up to count numbers in the range B5:B11:

=COUNT(B5:B11) // returns 4

COUNT returns 4, since there are 4 numeric values in the range B5:B11. Text values and blank cells are ignored.

Example #2 - constants

The example below shows COUNT with 3 hardcoded values. Two of the values are numbers, one is text, so COUNT returns 2:

=COUNT(1,2,"apple") // returns 2

Functions for counting

To count numbers only, use the COUNT function.

To count numbers and text, use the COUNTA function.

To count based one one criteria, use the COUNTIF function

To count based one multiple criteria, use the COUNTIFS function.

To count empty cells, use the COUNTBLANK function.

Notes

COUNT can handle up to 255 arguments.

COUNT ignores the logical values TRUE and FALSE.

COUNT ignores text values and empty cells.

**********************************************************************

COUNTIF FUNCTION

Summary 

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 

Count cells that match criteria

Return value 

A number representing cells counted.

Syntax 

=COUNTIF (range, criteria)

Arguments 

range - The range of cells to count.

criteria - The criteria that controls which cells should be counted.

Usage notes 

The COUNTIF function in Excel counts the number of cells in a range that match one supplied condition. Criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria can also be based on a value from another cell, as explained below.

COUNTIF is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIF requires a cell range, you can't use an array.

COUNTIF only supports a single condition. If you need to apply multiple criteria, use the COUNTIFS function. If you need to manipulate values in the range argument as part of a logical test, see the SUMPRODUCT and/or FILTER functions.

Examples

In the worksheet shown above, the following formulas are used in cells G5, G6, and G7:

=COUNTIF(D5:D12,">100") // count sales over 100

=COUNTIF(B5:B12,"jim") // count name = "jim"

=COUNTIF(C5:C12,"ca") // count state = "ca"

Notice COUNTIF is not case-sensitive.

Double quotes ("") in criteria

In general, text values need to be enclosed in double quotes (""), and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes, as seen in the second example below:

=COUNTIF(A1:A10,100) // count cells equal to 100

=COUNTIF(A1:A10,">32") // count cells greater than 32

=COUNTIF(A1:A10,"jim") // count cells equal to "jim"

Value from another cell

A value from another cell can be included in criteria using concatenation. In the example below, COUNTIF will return the count of values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes.

=COUNTIF(A1:A10,"<"&B1) // count cells less than  B1

COUNTIF with dates

The easiest way to use COUNTIF with dates is to refer to a valid date in another cell with a cell reference. For example, to count cells in A1:A10 that contain a date greater than the date in B1, you can use a formula like this:

=COUNTIF(A1:A10, ">"&B1) // count dates greater than A1

Notice we must concatenate an operator to the date in A1. To use more advanced date criteria (i.e. all dates in a given month, or all dates between two dates) you'll want to switch to the COUNTIFS function, which can handle multiple criteria.

The safest way hardcode a date into COUNTIF is to use the DATE function. This ensures Excel will understand the date. To count cells in A1:A10 that contain a date less than April 1, 2020, you can use a formula like this

=COUNTIF(A1:A10,"<"&DATE(2020,4,1)) // dates less than 1-Apr-2020

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=COUNTIF(A1:A5,"*apple*") // cells that contain "apple"

To count cells in A1:A5 that contain any 3 text characters, you can use:

=COUNTIF(A1:A5,"???") // cells that contain any 3 characters

The tilde (~) is an escape character to match literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

COUNTIF is not case-sensitive. Use the EXACT function for case-sensitive counts.

COUNTIF only supports one condition. Use the COUNTIFS function for multiple criteria.

Text strings in criteria must be enclosed in double quotes (""), i.e. "apple", ">32", "ja*"

Cell references in criteria are not enclosed in quotes, i.e. "<"&A1

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters (zero or more).

To match a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).

COUNTIF requires a range, you can't substitute an array.

COUNTIF returns incorrect results when used to match strings longer than 255 characters.

COUNTIF will return a #VALUE error when referencing another workbook that is closed.

**************************************************************************

COUNTIFS FUNCTION

Summary 

The Excel COUNTIFS function returns the count of cells that meet one or more criteria. COUNTIFS can be used with criteria based on dates, numbers, text, and other conditions. COUNTIFS supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching.

Purpose 

Count cells that match multiple criteria

Return value 

The number of times criteria are met

Syntax 

=COUNTIFS (range1, criteria1, [range2], [criteria2], ...)

Arguments 

range1 - The first range to evaulate.

criteria1 - The criteria to use on range1.

range2 - [optional] The second range to evaluate.

criteria2 - [optional] The criteria to use on range2.

Usage notes 

The COUNTIFS function in Excel counts the number of cells in a range that match one supplied criteria. Unlike the older COUNTIF function, COUNTIFS can apply more more than one condition at the same time. Conditions are supplied with range/criteria pairs, and only the first pair is required.  For each additional condition, you must supply another range/criteria pair. Up to 127 range/criteria pairs are allowed. 

Criteria can include logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. Criteria can also be based on a value from another cell, as explained below.

COUNTIFS is in a group of eight functions in Excel that split logical criteria into two parts (range + criteria). As a result, the syntax used to construct criteria is different, and COUNTIFS requires a cell range for range arguments, you can't use an array.

Examples

With the example shown, COUNTIFS can be used to count records using 2 criteria as follows:

=COUNTIFS(C5:C14,"red",D5:D14,"tx") // red and TX

=COUNTIFS(C5:C14,"red",F5:F14,">20") // red and >20

Notice the COUNTIFS function is not case-sensitive.

Double quotes ("") in criteria

In general, text values need to be enclosed in double quotes, and numbers do not. However, when a logical operator is included with a number, the number and operator must be enclosed in quotes as shown below:

=COUNTIFS(A1:A10,100) // count equal to 100

=COUNTIFS(A1:A10,">50") // count greater than 50

=COUNTIFS(A1:A10,"jim") // count equal to "jim"

Note: showing one condition only for simplicity. Additional conditions must follow the same rules.

Value from another cell

When using a value from another cell in a condition, the cell reference must be concatenated to an operator when used. In the example below, COUNTIFS will count the values in A1:A10 that are less than the value in cell B1. Notice the less than operator (which is text) is enclosed in quotes, but the cell reference is not:

=COUNTIFS(A1:A10,"<"&B1) // count cells less than  B1

Note: COUNTIFS is one of several functions that split conditions into two parts: range + criteria. This causes some inconsistencies with respect to other formulas and functions.

Wildcards

The wildcard characters question mark (?), asterisk(*), or tilde (~) can be used in criteria. A question mark (?) matches any one character, and an asterisk (*) matches zero or more characters of any kind. For example, to count cells in a A1:A5 that contain the text "apple" anywhere, you can use a formula like this:

=COUNTIFS(A1:A5,"*apple*") // count cells that contain "apple"

The tilde (~) is an escape character to allow you to find literal wildcards. For example, to count a literal question mark (?), asterisk(*), or tilde (~), add a tilde in front of the wildcard (i.e. ~?, ~*, ~~).

Notes

Multiple conditions are applied with AND logic, i.e. condition 1 AND condition 2, etc.

Each additional range must have the same number of rows and columns as range1, but ranges do not need to be adjacent. If you supply ranges that don't match, you'll get a #VALUE error.

Non-numeric criteria needs to be enclosed in double quotes but numeric criteria does not. For example: 100, "100", ">32", "jim", or A1 (where A1 contains a number).

The wildcard characters ? and * can be used in criteria. A question mark matches any one character and an asterisk matches any sequence of characters.

To find a literal question mark or asterisk, use a tilde (~) in front question mark or asterisk (i.e. ~?, ~*).