How to use Countifs function in Excel?

COUNTIFS Function

COUNTIFS is Microsoft Excel Function, it is used for COUNT all numbers or Text in range of cells in excel with multiple criteria.

how to use countifs function in excel

Inbuilt Function

COUNTIFS function is an inbuilt function in Microsoft Excel which is categorized as Statistical Function.

Operators & Wildcards

We can use operator (<, >, =, <>,>=, <=) and Wildcards (*, ?) for COUNTIFS function. Operators are used for logical operations and wildcards are used for the partial match.

Availability

COUNTIFS function available in Excel 2007 and its later version.

Syntax – There are 255 arguments in COUNTIFS Function. Except for first two syntaxes, all syntax is optional.

=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,……..,criteria_range127,criteria127)

 (A). Criteria_Range1 – The first range which is used to evaluate.

 (B). Criteria1 – The criteria1 used based on Criteria_Range1

 (C). Criteria_Range2 – This is optional. The Second range which is used to evaluate.

 (D). Criteria2 – This is optional. The criteria2 used based on Criteria_Range2

 

Criteria Apply -> We Count based on criteria value in cells. We use criteria from two methods. We use multiple criteria for COUNTIFS.

(I). ByVal – =COUNTIFS($B$2:$B$10,”A”), this is a fixed criteria value will not be changed while we use this for other criteria. We have to always change criteria manually.

(II). ByRef – =COUNTIFS($B$2:$B$10,D2), this is flexible criteria value will change while using this for other criteria.

How to use Countifs function in Excel?

Rules for using COUNTIFS

(A). Each Range must equal to the first Criteria_Range1.

(B). Non-Numeric criteria must be in double quotes i.e “Criteria”, but numeric criteria doesn’t require double quotes except with operator i.e “>180”.

(C). The wildcards (*, ?) can be used as criteria in double quotes. Star (*) represent any series of characters and question mark (?) to represent any single of characters.

NOTE

(A). We cannot use Vertical Range and Horizontal Range as Criteria Range for COUNTIFS Function. If we use it then the COUNTIFS Function gives an error.

(B) When we criteria as text its length, should not be more than 255 characters.

Download Workbook

How to use COUNTIF function in Excel?

Leave a Reply

Your email address will not be published. Required fields are marked *