Advanced Use of COUNTIFS Function in Excel (9 Examples)

The COUNTIFS function is a built-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 uses of this COUNTIFS function in Excel with proper illustrations.

Advanced Uses of COUNTIFS Function in Excel: 9 Examples

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

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.

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. 

Example 2: Combine COUNTIFS with EDATE Function to Count Values That Meet a Date Criteria

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.

• 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.

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

Read More: How to Use COUNTIFS with Date Range in Excel

Example 3: Apply COUNTIFS Function with Multiple OR Criteria in Different Columns

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.

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.

Example 4: Use of COUNTIFS Function to Sum with OR Logic

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.

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

Example 5: Advanced Use of COUNTIFS Function with Wildcards in Excel

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.

We get this result because of the wildcard symbol.

Example 6: Apply COUNTIFS Based on Current Day

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.

Successfully get the result.

Example 7: Insert Numerical and Text Criteria Within COUNTIFS Function

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

📌 Steps:

• Now, insert the formula on Cell C17.

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

Example 8: Use of COUNTIFS with Named Range in Excel

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.

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

Example 9: Apply COUNTIFS Function in an Excel Table

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.

We get the same result after using the table.

Download Practice Workbook

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

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 give your suggestions in the comment box.

Related Articles

• How to Use COUNTIFS for Cells Not Equal to Multiple Text in Excel
• How to Use COUNTIFS Function with 3 Criteria in Excel

<< Go Back to Excel COUNTIFS Function | Excel Functions | Learn Excel