COUNIFS Function
The Excel COUNTIFS function works for give the count of cells that meet one or more criteria. COUNTIFS can be used to count cells that contain dates, numbers, and text, with logical operators (>,<,<>,=).
Syntax
=COUNTIFS(range1, criteria1, [range2], [criteria2], …) and so on, which can check things like
Usage
The COUNTIFS function is a common, widely used function in Excel, and can be used to count cells that contain dates, numbers, and text.
Example COUNTIFS function
Find the greater than sale of Product type:
The conditions are that the type is “Soap” and Sale is >9000.
Example COUNTIFS function, step by step:
- Select the cell H2
- Type =COUNTIFS
- Click the COUNTIFS command
- Select Specify the range for the type A2:B13 (the Product)
- Select Specify the criteria (the cell F2, which has the value “SOAP”)
- Select Specify the range for the second condition B2:B13 (the Sale values)
- Select Specify the SING “>” & criteria (the cell G2, which has the value “9000”)
- Hit enter
Now, we can see the total Count number of product type and sale is greater than 9000 on H column.
In COUNTIFS function we can use following function also
- Cells equal to “text”
Formula : COUNTIFS(C5:C14,”F2″,D5:D14,”Text”)
- Cells greater than 20
Formula : COUNTIFS(C5:C14,”F2″,D5:D14,”>20″)
In general, text values need to be enclosed in double quotes, and numbers do not.
=COUNTIFS(A1:A10,9000) “ count equal to 9000”
=COUNTIFS(A1:A10,”>500″) “count greater than 500”
=COUNTIFS(A1:A10,”Soap”) “count equal to “Soap”
Not equal to
=COUNTIFS(A1:A10,”<>red”) “not “red”
Blank cells
=COUNTIFS(A1:A10,”<>”) “not blank”
=COUNTIFS(A1:A10,””) “blank”
Wildcards
Note : Wild card means if text is between an word or in start then this function will use
Now result will come as follows for wildcard and countifs formula
