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.

## Leave a Review