COUNTIF

Count reference cells based on criteria in Excel
Counts the number of cells that meet the criteria you specify in the argument

Rational

Count reference cells based on criteria.

Description

The Excel COUNTIF function returns a count of number based on given criteria. For example, if A1:A5 contains a list of digits (2,8,10,5,6) in a column and user want to count numbers that are more than 5, the formula can be =COUNTIF(A1:A5,”>5″) that count numbers in the list >5 and return 3.

Syntax

=COUNTIF(range, criteria)

Parameters

range – The range of cells to evaluate the conditions (criteria)
criteria – The rule to apply for additio.

Return Value

Count of values based on a criterion

Key Notes

  • The COUNTIF function counts numerical value from a reference range within the scope of given matching criteria of selection.
  • To count with multiple conditions use the COUNTIFS function.
  • For text criteria that include logical operators (>,<,<>,=) or wildcards symbols (*,?) for partial matches must be enclosed in double quotation marks (“) like “<30”.
  • The user can find a literal question mark or asterisk by using a tilde (~) symbol before a question mark or asterisk (i.e., ~?, ~*). A criteria a*a will match with all cells containing a string beginning with “a” and ending in “a” like a text “abide” or “abate”.

Example

To count unique values based on condition for example here to count  how many times Pastry comes in the range. we use formula =COUNTIF($E4:E14,I6) it return 4 as Pastry comes 4 times in the list.

Keep Reading Similar Functions:

Sum range of cells based on multiple criteria in Excel

SUMIFS

The SUMIFS function returns an addition of numerical as per multiple criteria based on the match of dates, numbers, and text against the reference.

Read More »
Counts cells that are not blank in Excel

COUNTA

The COUNTA function count of numbers and text within the reference list of arguments ignoring blank cells, =COUNTA(A1:A10) will return 7 (disregard blank).

Read More »
Find the average of numbers in Excel

AVERAGEIF

The AVERAGEIF function returns an average figure based on given criteria like =AVERAGEIF({6;8;10;3;5},”>5″) average numbers that are more than 5 and return 8.

Read More »
Count reference cells based on multiple criteria in Excel

COUNTIFS

The COUNTIFS function returns a count of numbers based on one or multiple given criterion based on dates, numbers, and text.

Read More »
Sum range of cells based on the rule in Excel

SUMIF

The SUMIF function returns a sum of number based on given criteria, like =SUMIF({2;8;10;3;5},”>5″) add numbers that are only more than 5 and returns 18.

Read More »
Counts cells with only Numbers in Excel

COUNT

The COUNT function count of numbers within the reference list of arguments ignoring text & blank cells, =COUNT(A1:A10) will return 5 (only numeric).

Read More »