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:

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:

• Press Enter.

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

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

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:

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

• Hit Enter.

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

Read more: Excel COUNTIFS with Multiple Criteria and OR Logic

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:

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

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

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

• Press Enter.

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

Download the Practice Workbook

Related Articles

• COUNTIFS Function in Excel with Multiple Criteria from Different Sheet
• Excel COUNTIFS with Multiple Criteria Including Not Blank

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