In this post, you’ll learn about COUNTIFS Function, its syntax and how to use COUNTIFS Function in your excel spreadsheet.
What is COUNTIFS Function?
COUNTIFS Function in Excel calculates the counting of cells that meets multiple conditions and returns number of times met with the criteria. Arguments can be dates, numbers, text, cell references and other conditions.
COUNTIFS Function supports logical operators(<, >, <>, =) and wildcards(*, ?) for partial matching. COUNTIFS function introduced in MS Excel 2007 and available in later version.
In financial analysis, COUNTIFS helps in quick analysis.
Syntax
=COUNTIFS(criteria_range 1, criteria 1, [criteria_range 2, criteria 2],..)
Arguments:
- = – built-in function.
- COUNTIFS() – function name.
- criteria_range 1 – (required) this is the first range to calculate criteria.
- criteria 1 – (required) the cell to be counted.
- criteria_range 2, criteria 2 – (optional) this is the additional ranges and criteria for calculation.
The values can be up to 127 range/ criteria pairs.
COUNTIFS Function is in a group of eight function in Excel that splits logical values into two parts(range + criteria)
Remark:
You can use COUNTIFS Function in single as well as multiple conditions of ranges.
Note:
- Counts the cells that meet the given conditions.
- Additional ranges must also have the same number of rows and columns.
- If the criteria is an empty cell, then COUNTIFS Function consider it as zero.
- Multiple conditions are applies with AND logic(when conditions are TRUE), that is condition 1 AND condition 2 etc.
- #VALUE! Error – if no match in criteria.
Difference between COUNTIF and COUNTIFS Function
| COUNTIF Function | COUNTIFS Function |
| It calculates with one range. | It calculates different criteria in same or multiple ranges. |
| Counts cells with single condition | Counts cell with single and multiple conditions |
How to use COUNTIFS Function in Excel?
COUNTIFS Function counts the cells that meet with a single and multiple condition.
DOUBLE QUOTES (“”):
- Text values are must be within double quotes, but numbers are not. If the logical operator with a number, the number and operator must be within quotation mark.
- Value from another cell – value from another cell by using catenation.
- WILLCARDS:
The wildcards characters question mark (?), asterisk (*), or tilde (~) can used in criteria.
The question mark (?) matches all the single characters.
The asterisk (*) matches all sequences of characters.
The tilde (~) is an escape character it allows you to find the literal wildcard.
Not equal to – Use “<>” operator within double quotes to construct “not equal to” criteria.
Blank cells – COUNTIFS Function counts the cells that are blank and not blank.
Dates – the safest way of using dates is by using Data Function.
Examples:
Step 1:
Open the workbook in your Microsoft Excel.
Step 2:
Enter the data, in the workbook.
In this example, the given data is Name of the customers, the date, place and sales amount of product purchased.
STEP 3:
In the new cell, give the formula or the syntax. Start with ‘ =’ for every functions, COUNTIFS to initiate functions name, followed by open parenthesis.
According to the example, criteria_range 1 is Name of the customers, which is from A3 to A12.
STEP 4:
After calculating criteria_range 1, put a comma. Next step is calculating criteria 1.
Here, criteria 1 is Sarah. Since it is a text value is enclosed within double quotes.
STEP 5:
Keep a comma after calculating criteria 1. Next is to calculate criteria_range 2.
criteria_range 2 is Place, which is from C3 to C12.
Step 6:
Followed by comma, next is calculating criteria 2.
criteria 2 is Odisha, since it is a text value it is enclosed within double quotes.
STEP 7:
Press enter to get the calculated results.
Return 2. Sarah has purchased the products 2 times at Odisha.
