How to Use COUNTIFS to Count Across Multiple Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

countifs across multiple columns


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

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.

Using COUNTIFS to Count Cells across Multiple Columns Under Different AND Criteria

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

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


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.

Using COUNTIFS to Count Cells across Separate Columns Under Single Criteria


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.

Using COUNTIFS to Count Cells across Distinct Columns Under Different OR Criteria

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


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

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.

Incorporating COUNTIFS with Wildcard Characters to Count Cells across Different Columns

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


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.


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


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.


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.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

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

6 Comments
  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?

  4. Reply Lutfor Rahman Shimanto
    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.
    https://www.exceldemy.com/wp-content/uploads/2023/01/To-Count-the-Number-of-Components-for-Each-Station.xlsx
    Best regards,
    Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo