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