There are many default **Excel functions** that we can use to create formulas. The **COUNTIFS** function provides plenty of opportunities to count cells under different criteria across multiple columns or rows. In this article, Iâ€™ll try to illustrate with a relatable dataset how we can utilize the **COUNTIFS** function precisely to count cells across multiple columns under a number of suitable criteria.

**Table of Contents**hide

## Download Practice Workbook

You can download the Excel Workbook that we used to prepare this article. You can modify the values & see new outputs with embedded formulas.

## Introduction to COUNTIF Function

Before getting down to the uses of** COUNTIFS**, letâ€™s have a look at how **the COUNTIF function** works since **COUNTIFS** is a subcategory of **the COUNTIF function**. The fundamental difference between these two functions is that **COUNTIFS** simply works under more than one criterion while COUNTIF returns values based on a single criterion only.

**Formula Syntax:**

`COUNTIF(range, criteria)`

**Arguments:**

**range-** Range of cells to be selectedÂ

**criteria-** Criteria of the cells that need to be assigned

**Function:**

Counts the number of cells within the range that meet the given condition.

**Example:**

In the picture below, we give a list of color names. If we want to know how many times Red is there then we have to type in the output cell-

`=COUNTIF(B2:B11,"Red")`

After pressing **Enter**, weâ€™ll see there are 4 instances of Red in the list.

## 7 Suitable Ways to Use COUNTIFS Across Multiple Columns

In the following sections, we will use **the COUNTIFS function** for different criteria such as counting cells for different AND criteria, OR criteria, from multiple columns array.

### 1. Using COUNTIFS to Count Cells Across Multiple Columns Under Different AND Criteria

Our dataset consists of the sales profits of several brand computers. In our first criterion, weâ€™ll determine how many HP desktops have been sold with more than $100 profit. Therefore, learn the following steps.

**ðŸ“Œ**** Steps:**

- Firstly, in
**Cell F16**for our dataset, we have to type-

`=COUNTIFS(B5:B27,B6,C5:C27,C6,D5:D27,">100")`

- Then, press
**Enter**& youâ€™ll see a total of 2 counts of HP desktops with more than $100 profits.

When weâ€™re adding multiple criteria in the **COUNTIFS** function, we have to use a **Comma(,)** between two criteria inside the function.

**Read more:** **COUNTIF with Multiple Criteria in Different Columns in Excel**

### 2. Using COUNTIFS to Count Cells Across Separate Columns Under Single Criteria

Now in this section, weâ€™ll execute the formula with similar criteria but in different columns. In **Columns D & E**, Estimated & Final Profits are recorded respectively. Weâ€™re going to find the number of cases Estimated & Final profits both have more than $100.

**ðŸ“Œ**** Steps:**

- First, in
**Cell G16**, type-

`=COUNTIFS(D5:D27,">100",E5:E27,">100")`

- After that, press
**Enter**& youâ€™ll see a total of 10 findings for our criteria.

**Read more:** **Excel COUNTIFS Function with Multiple Criteria in Same Column**

### 3. Using COUNTIFS to Count Cells Across Distinct Columns Under Different OR Criteria

Weâ€™ll determine now how many HP desktops, as well as Lenovo Notebooks, have been sold. It means our formula will now incorporate multiple criteria from multiple columns with **OR** logic. So, follow the below steps.

**ðŸ“Œ**** Steps:**

- First of all, the formula for this criterion in
**Cell F17**will be-

`=COUNTIFS(B5:B27,"HP",C5:C27,"Desktop") + COUNTIFS(B5:B27,"Lenovo",C5:C27,"Notebook")`

- After pressing
**Enter**, the resultant value will be 6. So there are 6 instances of HP desktops & Lenovo notebooks in our dataset.

While working with multiple **OR** criteria, we have to add two different criteria with a **Plus(+)** between two different **COUNTIFS** functions.

**Read more: Excel COUNTIFS with Multiple Criteria and OR Logic (3 Examples)Â **

### 4. Combining COUNTIFS and SUM Functions to Count Cells Across Multiple Columns from Array

While working with both **AND & OR** criteria, we have to use the **SUM** function outside the **COUNTIFS** function as here the **COUNTIFS** function will return will multiple results that need to be added up. Weâ€™ll find out how many HP or Lenovo desktops have more than $100 in profits.

**ðŸ“Œ**** Steps:**

- In the Beginning, in
**Cell F16**, we have to type-

`=SUM(COUNTIFS(B5:B27,{"HP","Lenovo"}, C5:C27,"Desktop",D5:D27,">100"))`

- Subsequently, press
**Enter**& the function will return as 4.

**ðŸ”Ž**** How Does This Formula Work?**

**COUNTIFS**function returns with the values in an array & the values are-**{2,2}**- Lastly, the
**SUM**function then simply sums these values up to**4(2+2).**

**Read More: ****COUNTIFS with Multiple Criteria**

**Similar Readings**

**Count Unique Values with Criteria by SUMPRODUCT in Excel****COUNTIF Multiple Ranges Same Criteria in Excel****COUNTIF between Two Cell Values in Excel (5 Examples)**

### 5. Incorporating COUNTIFS with Wildcard Characters to Count Cells Across Different Columns

In this section, our dataset is not complete. A number of entries are missing. Weâ€™ll find out the number of complete entries here.

**ðŸ“Œ**** Steps:**

- Firstly, in
**Cell F16**, type-

`=COUNTIFS(B5:B27,"*",C5:C27,"*",D5:D27,"<>"&"")`

- Consequently, press
**Enter**& youâ€™ll find a total of 10 complete entries through this function.

Here, weâ€™re using a Wildcard character here & that is **Asterisk(*)**. It is used to find text strings in a range of cells. Thus, we have to put it within **Double-Quotes** inside the function.

**Read More:**** Excel COUNTIFS Not Working with Multiple Criteria (2 Solutions)**

### 6. Counting Cells with COUNTIFS Function Under Dates ConditionÂ Across Distinct Columns

With the **COUNTIFS** function, we can also deal with a date input. For our dataset, weâ€™ll now find how many notebooks we purchased in June 2021.

**ðŸ“Œ**** Steps:**

- In
**Cell F16**, we have to type-

`=COUNTIFS(C5:C27,"Notebook",D5:D27,">=6/1/2021")`

- Afterward, press
**Enter**. - Accordingly, the result will be 10.

While inputting a date inside a function, we have to maintain the format as **MM/DD/YYYY**. With Greater or Less Than symbols you can easily insert the logic for date criteria.

**Read More:** **Exclude Multiple Criteria in Same Column with SUMIFS Function**

### 7. Using COUNTIFS with TODAY Function to Count Cells Across Multiple Columns

We can also insert the **TODAY** function while working with the **COUNTIFS** formula. By using the **TODAY** function we can determine preceding or following events to count from the current date. So, for our dataset below, weâ€™ll find out how many devices were purchased in June & how many of them are yet to deliver from the current date **(While preparing this section for the article, the current date was 7/7/2021)**.

**ðŸ“Œ**** Steps:**

- Firstly, in
**Cell G16**, the formula for our criteria will be-

`=COUNTIFS(D5:D27,">6/1/2021",E5:E27,">"&TODAY())`

- Next, press
**Enter**. - Therefore, youâ€™ll notice a total of 7 deliveries that are left.

While using the **TODAY** function inside the **COUNTIFS** formula, we have to add it with conditions by using **Ampersand(&)** between them.

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

## Concluding Words

I hope, the above uses of the **COUNTIFS** function under multiple criteria across different columns will help you to apply it with ease in your regular work with MS Excel. If you think Iâ€™ve missed a point or anything to add to this article then please let me know in the comment section. Follow **the ExcelDemy** website for more articles like this.

**Related Articles **

**How to Use COUNTIF to Count Cells Greater Than 0****Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function****COUNTIFS Function in Excel with Multiple Criteria from Different Sheet****Excel COUNTIFS with Multiple Criteria Including Not Blank****SUMIFS with INDEX-MATCH Formula Including Multiple Criteria**

I am trying to get a count for an inventory sheet using a 90 day grace period. Basically count is a cell’s date is 90 days or less away from Toda(). From there I would use the answer cell to determine if I need to order anything if any value is greater than zero.

Any help would be much appreciated.

This is the formula I have been playing with based off your article above.

=COUNTIFS(L5,”<="&TODAY()+90,O5,"<="&TODAY()+90,R5,"<="&TODAY()+90, U5,"<="&TODAY()+90, X5,"<="&TODAY()+90, AA5,"<="&TODAY()+90)

Hi Brennan,

Thanks for your comment. You cannot use any function in the

â€˜criteriaâ€™field of theCOUNTIFfunction. You must have to input a specifictextorvalue. Moreover, you have to mention a range of cells in theâ€˜criteria_range 1â€™field, where the function count for your desired data. If you input a single cell instead of a range of cells, theCOUNTIFfunction will not show the sum of the total count.You can consider some other Excel functions like

VLOOKUPandTODAYfunctions to get the decision if your worksheet allows you to place the value of cells L5, O5, R5, U5, X5, and AA5 in the conjugative cells whether row-wise or column-wise. I am telling you the process below.First, set two criteria. As you want to show the value less or equal to 90 days, so you can set 90 for <= 90 days and 91 for >90 days.

Then, using the

VLOOKUPandTODAYfunctions, write down the formula:=VLOOKUP(TODAY()-[Cell Ref],Criteria,2,TRUE)Here,

[Cell Ref] stands for your desired cell

â€˜Criteriaâ€™ is the table array name that I mentioned in the first step.

2 is the col_indes_number. This number tells the function which column value of the criteria table we want to show.

As you get the decision of the VLOOKUP function, whether it is more than 90 days or less than 90 days, use the COUNTIF function to get the total count of less than or equal to 90 days.

=COUNTIFS([Cell Ref. Range,”<= 90 Days")Here,

“<= 90 Days" is the desired criteria. For a better demonstration of this procedure, you can also look at one of our similar types of articles,

How to Use Stock Ageing Analysis Formula in Excel.If you are still facing any problems, please inform us.

Thank you. This was a very helpful explanation for me.

Hello,

ExcelAmateur!Thanks for your appreciation. To get more helpful content with explanations stay in touch with

ExcelDemy.Regards

ExcelDemyI want to do a countifs with Or logic instead or And logic.

I we use the sheet from number 2 as an example I would want it to count if Estimated Profit or Total Profit was above 100.

So it should return 16, if my count is right.

My data is two different sheets. I am filling out one sheet based on another sheet.

There’s one station per row in the output sheet, the input sheet has one component per row. I want to count how many components are in each station. There’s three different columns that could contain the station ID. Poor data quality means it could only be in one cell or it could be in all three cells. So I want to count if any of the columns contain the ID. I know how to count if the ID is in all columns or I can count each cell that contains the ID.

I will probably make a new column that combines the three columns and just look if that contain the station ID. But it’s not an elegant solution. Any ideas?

Hello ARON HOLMBERG

Thank you for reporting your issues. To count the number of components for each station, when the Station ID may be in one of three different columns, you can use the SUMPRODUCT function.

=SUMPRODUCT((InputSheet!$B:$B=B5)+(InputSheet!$C:$C=B5)+(InputSheet!$D:$D=B5))

This formula will test each of the three columns for the station ID and return 1 if it’s present in any of the columns and 0 if it’s not. Then, SUMPRODUCT will sum up the results, giving you the components for the station.

This solution is more elegant than creating a new column that combines the three columns, as it avoids the need to manipulate the data. If you would like a copy of the illustrated workbook, please click the link provided below this section.

https://www.exceldemy.com/wp-content/uploads/2023/01/To-Count-the-Number-of-Components-for-Each-Station.xlsx

Best regards,

Lutfor Rahman Shimanto