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:
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).
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.
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.
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.
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).
COUNTIFS
The COUNTIFS function returns a count of numbers based on one or multiple given criterion based on dates, numbers, and text.