How to Use COUNTIFS to Count Across Multiple Columns in Excel

Introduction to the COUNTIF Function

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

We have a list of color names. To know how many times Red is there, we’ll use the following:

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

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

How to Use COUNTIFS to Count Across Multiple Columns in Excel: 7 Suitable Ways

Method 1 – Using COUNTIFS to Count Cells Across Multiple Columns Under Different AND Criteria

Our dataset consists of the sales profits of several brand computers. Weâ€™ll determine how many HP desktops have been sold with more than \$100 in profit.

Steps:

• In cell F16, insert the following:
`=COUNTIFS(B5:B27,B6,C5:C27,C6,D5:D27,">100")`
• Press Enter.

When adding multiple criteria in the COUNTIFS function, use a Comma(,) between two criteria inside the function.

Method 2 – Using COUNTIFS to Count Cells Across Separate Columns Under Single Criteria

Weâ€™ll execute the formula with similar criteria but in different columns. Columns D and E contain the Estimated and Final Profits are recorded respectively. Weâ€™re going to find the number of cases where both Estimated and Final profits are over \$100.

Steps:

• In cell G16, insert:
`=COUNTIFS(D5:D27,">100",E5:E27,">100")`
• Press Enter.

Method 3 – Using COUNTIFS to Count Cells Across Distinct Columns Under Different OR Criteria

Weâ€™ll determine now how many HP desktops and Lenovo Notebooks have been sold.

Steps:

• The formula in Cell F17 will be-
`=COUNTIFS(B5:B27,"HP",C5:C27,"Desktop") + COUNTIFS(B5:B27,"Lenovo",C5:C27,"Notebook")`
• Hit Enter.

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

Method 4 – Combining COUNTIFS and SUM Functions to Count Cells Across Multiple Columns from an Array

Weâ€™ll find how many HP or Lenovo desktops have more than \$100 in profits.

Steps:

• In cell F16, use the following formula:
`=SUM(COUNTIFS(B5:B27,{"HP","Lenovo"}, C5:C27,"Desktop",D5:D27,">100"))`
• Press Enter.

How Does This Formula Work?

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

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

Steps:

• In Cell F16, insert:
`=COUNTIFS(B5:B27,"*",C5:C27,"*",D5:D27,"<>"&"")`
• Press Enter.

Weâ€™re using a Wildcard character Asterisk(*). It is used to find text strings in a range of cells. We have to put it within double quotes inside the function.

Method 6 – Counting Cells with the COUNTIFS Function and a Date Condition Across Distinct Columns

Weâ€™ll find how many notebooks were purchased in June 2021.

Steps:

• In Cell F16, use:
`=COUNTIFS(C5:C27,"Notebook",D5:D27,">=6/1/2021")`
• Press Enter.

While inputting a date inside a function, maintain the format as MM/DD/YYYY.

Method 7 – Using COUNTIFS with the TODAY Function to Count Cells Across Multiple Columns

Weâ€™ll determine how many devices were purchased in June and how many of them were yet to be delivered on the current date.

Steps:

• In Cell G16, the formula is:
`=COUNTIFS(D5:D27,">6/1/2021",E5:E27,">"&TODAY())`
• Press Enter.

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

Related Articles

Get FREE Advanced Excel Exercises with Solutions!

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.Â  In this role, he creates techy content all about Excel... Read Full Bio

1. 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 the COUNTIF function. You must have to input a specific text or value. 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, the COUNTIF function will not show the sum of the total count.
You can consider some other Excel functions like VLOOKUP and TODAY functions 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 VLOOKUP and TODAY functions, 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.

2. 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
ExcelDemy

3. I 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?

Lutfor Rahman Shimanto Jan 31, 2023 at 4:01 PM

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.