The **COUNTIFS function** is a built-in-in function of Excel. It is more advanced than the **COUNTIF function**. The **COUNTIF **function satisfies only one criterion in a single range. But the **COUNTIFS **can satisfy multiple criteria with multiple ranges. In this article, we will discuss some advanced use of this **COUNTIFS **function in **Excel **with proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**9 Advanced Uses of COUNTIFS Function in Excel**

We will discuss **9** advanced uses of the **COUNTIFS** function in the below section. We will consider the following dataset for this purpose.

__Example 1:__** Count Cells Between Two Numbers Using COUNTIFS Function**

__Example 1:__

In the first example, we will see how to count the number of cells between two specified numerical values in Excel using this function.

**📌 ****Steps:**

- First, add a row to the dataset that contains two cells,
**B11:D11**. Name the left suitably, and keep the right cell blank for now.

- Now, put the following formula on
**Cell D11**.

`=COUNTIFS(C5:C9,"<350",C5:C9,">200")`

Here, we applied two conditions to count the number of rows where the price is between **$350** to** $200**. We apply multiple conditions in the same column in this method.

**💬 Note:**

Always write greater or less than criteria inside double quotes **“”**, i.e. **“>200”**, or **“>=300”**, etc.

**Read More:** **How to Use COUNTIFS Function with 3 Criteria in Excel**

__Example 2:__ **Combine COUNTIFS with EDATE Function to Count Values That Meet a Date Criteria**

__Example 2:__

**EDATE function**returns the serial number of the date which is the indicated number of months before or after the start date.

Here, we want to insert date values in the **COUNTIFS** function using cell reference. We also used the **EDATE** function for previous or future dates.

**📌 ****Steps:**

- We modified the dataset. We added the expiry date of the products.

In the result section, we insert **3** dates. We want to know the number of products those will expiry date from the mentioned date to **1** month later. It will be suitable for the users if present the date in terms of the month’s name. We can also insert the month’s name here. But the **EDATE** function will not recognize the month name. Look how we convert the date in terms of the month.

- Select
**Range B12:B14**. - Press the keyboard shortcut
**Ctrl+1**for**Format Cells**. - Choose the
**Custom**option from the**Number**tab. - Finally, input the desired format in the
**Type**box and press**OK**.

- Look at the dataset.

Dates are shown in terms of their corresponding month’s name.

- Put the following formula on
**Cell C12**.

`=COUNTIFS($E$5:$E$9,">="&B12,$E$5:$E$9,"<"&EDATE(B12,1))`

- Then, press the
**Enter**button and drag the**Fill Handle**icon.

The result is after applying this formula.

**💬 Note:**

- We can also use the
**DATE function**to insert date values in the formula.

`=COUNTIFS($E$5:$E$9,">="&DATE(2022,10,1),$E$5:$E$9,"<"&EDATE(DATE(2022,10,1),1))`

- There is also an option to insert dates with hardcodes.

`=COUNTIFS($E$5:$E$9,">="&"1/10/2022",$E$5:$E$9,"<"&EDATE("1/10/2022",1))`

**Read More:** **How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)**

__Example 3:__** Apply COUNTIFS Function with Multiple OR Criteria in Different Columns**

__Example 3:__

Here, we will use the **COUNTIFS** function to perform **OR** operation. We will apply multiple criteria based on different columns.

**📌 ****Steps:**

- We set a condition for this method.

We want to get the number of cells containing orange, that is delivered or pending.

- Now, put the below formula on
**Cell C17**.

`=COUNTIFS($C$5:$C$14, "Orange", $E$5:$E$14,"Delivered") + COUNTIFS($C$5:$C$14, "Orange",$E$5:$E$14,"Pending")`

Here, we combined two **COUNTIFS** functions and get their or result.

The **1st** part of this formula returns the number of orange bills that are delivered, and the **2nd** part returns the number of orange bills that are pending.

**Read More:** **Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)**

__Example 4:__ Use of **COUNTIFS Function to Sum with OR Logic**

__Example 4:__Use of

**SUM function**adds all the numbers in a range of cells.

We will combine **SUM** and **COUNTIFS** functions here. We will also apply the conditions in an array form in the formula.

**📌 ****Steps:**

- We set a condition that which products are delivered or in the pending position.

- Insert the following formula on
**Cell C17**.

`=SUM(COUNTIFS($E$5:$E$14,{"Delivered","Pending"}))`

In the return, we get the sum of delivered and pending products.

__Example 5:__** Advanced Use of COUNTIFS Function with Wildcards in Excel**

__Example 5:__

Here, we will use a **wildcard** with the **COUNTIFS** function.

**📌 ****Steps:**

- We set a condition that which cells contain both
**Bill No.**and**Price**. - We used the wildcard for
**Bill No.**and not equal to the**Price**column. - Put the formula on
**Cell C17**.

`=COUNTIFS($B$5:$B$14,"*",$D$5:$D$14,"<>"&"")`

We get this result because of the wildcard symbol.

__Example 6:__** Apply COUNTIFS Based on Current Day**

__Example 6:__

**TODAY function**returns the current date formatted as a date.

Here, we will combine the **TODAY** function with the **COUNITIFS** function.

**📌 ****Steps:**

- We want to know the products that are packaged before the present day and expire after the present day.
- Input the following formula on
**Cell C12**.

`=COUNTIFS(E5:E9, "<"&TODAY(),F5:F9, ">"&TODAY())`

Successfully get the result.

__Example 7:__** ****Insert Numerical and Text Criteria Within COUNTIFS Function**

__Example 7:__

Here, we will insert numeric and text references in the **COUNTIFS** function.

**📌 ****Steps:**

- Now, insert the formula on
**Cell C17**.

`=COUNTIFS($C$5:$C$14, "Orange", $D$5:$D$14,"<=200")`

We get this result after inserting both numeric and text references in Excel.

__Example 8:__** Use of COUNTIFS with Named Range in Excel**

__Example 8:__

In this section, we will use the named range with the **COUNTIFS** function.

**📌 ****Steps:**

- First, we will show how to name a range in Excel.
- Select the data range of
**Bill No.**column. - Then, go to the
**Define Name**section of the**Formulas**tab.

- The
**New Name**window appears. - Now, put a name in the
**Name Box**. - We can also see the selected range in the
**Refers to**section.

We can also define names in a simple way.

- Just select the data range of the
**Name**column and put a name in the**Name Box**at the left-upper side of the sheet. - Then, press the
**Enter**button.

- Similarly, set the name of the data range of the
**Price**column.

- Insert the following formula on
**Cell C17**.

`=COUNTIFS(Fruit,"Orange",Price,"<=200")`

In this formula, we used the named range as the reference.

__Example 9:__** Apply COUNTIFS Function in an Excel Table**

__Example 9:__

The table is a very useful feature of Excel. Here, we will use this Tabel to create a formula in Excel.

**📌 ****Steps:**

- First, we will show how to create a table in excel.
- Select the whole dataset.
- Press
**Ctrl + T**to craete a table. - We can see the selected range in the
**Create Table**window. - Press the
**OK**button there.

We also marked the **My table has headers** option.

- We can see the table has been formed successfully.

- Now, put the formula based on the Excel table on
**Cell C17**.

`=COUNTIFS(Table1[Name],"Orange",Table1[Price],"<=200")`

We get the same result after using the table.

**Conclusion**

In this article, we described some advanced uses of the **COUNTIFS** function in Excel with **Excel Table**, **Named Range**, etc. I hope this will satisfy your needs. Please have a look at our website **ExcelDemy** and give your suggestions in the comment box.