Excel has two highly similar functions that you can use to count cells that match a conditional. These functions are useful for counting sales by employee, checking how many purchases were over a specific threshold, checking how many expenses came after a given date, and much more.

They provide a considerable amount of utility to Excel users, but knowing which to use and when makes creating spreadsheets and reports far simpler.

What Is COUNTIF in Excel?

Excel’s COUNTIF function is a quick and easy way to total cells within a range that meets a specific condition.

The Arguments for COUNTIF 

        COUNTIF(range, criteria)

The COUNTIF function is simple, taking only 2 arguments. The first argument is the range that you want to check the conditional against. Each cell in the range will be checked, and if it meets the requirement, the total count will be incremented by 1.

The second argument that the function requires is the test that you would like to use. The argument can be either a set piece of text or a reference to another cell on the spreadsheet.

By default, if the test doesn’t start with an operator, Excel will test to see if the cells in the range are exactly equal to the value. If you prefer to check to see if they are greater than, less than, or not equal to the value, you can start the expression with a symbol. You can also use “wildcards” in Excel to allow partial matches to be counted towards the total.

COUNTIF Examples

As an example, consider a list of sales from a single day for a small business. The COUNTIF function is a quick and easy way to pull simple data out of the list.

A daily sales report featuring one day of sales for 4 separate employees.

In column F, we can use the UNIQUE function to get a quick list of every employee who made a sale today: 

        =UNIQUE(B2:B25)

Then, in column G, the COUNTIF function can tell us exactly how many sales each employee made: 

        =COUNTIF(B2:B25, F3:F6)

The array formula in Excel will look at each of the 4 cells in F3:F6 and check each sale to see if the Cashier matches that name. The array formula gives us a quick total of how many sales each cashier made.

We can also check the size of each of the sales. In column G, you can use two functions to see how many sales were over $200.00 and how many were less: 

        =COUNTIF(C2:C25, ">200")
    
        =COUNTIF(C2:C25, "<=200")

Note that in the second function, we use the “<=” operator to ensure that any sales of exactly $200.00 don’t get skipped.

What Is COUNTIFS in Excel?

Excel’s COUNTIFS function looks similar to the COUNTIF function. The major difference is that it can accept more than one conditional statement to check data against.

The Arguments for COUNTIFS 

        COUNTIFS(range1, criteria1, range2, criteria2)

The COUNTIFS statement does not have a set number of arguments. It can accept any number of argument pairs. These pairs are always in the form of Range, then Conditional. For each of these pairs that you enter, the function will check each cell in the range to see if it meets the conditional.

All the cells with matching locations must meet their respective conditional for the total count to be incremented by one. This means that if you are testing three different cells in a single row, all three need to meet their conditional in order to go up.

​​COUNTIFS Examples

In our previous example, we looked at a set of sales for one day by a group of four employees. COUNTIFS makes finding out how many large sales each employee made simple.

A daily sales report for four employees with the number of large sales and small sales for each employee on the side.

In column G, you can use the following functions to determine how many large and small sales Steve made that day: 

        =COUNTIFS(B2:B25, F2, C2:C25, ">200")
    
        =COUNTIFS(B2:B25, F2, C2:C25, "<=200")

For each cell in B2:B25, it checks to see if the name matches the name in cell F2. If so, it then checks the matching row in C2:C25 to see if it is more or less than $200.00. If both conditions match, then the count is incremented by 1.

Excel’s COUNTIF and COUNTIFS: What Are the Differences?

Both COUNTIF and COUNTIFS serve very similar purposes. Both are used to count the number of cells that match conditions. COUNTIF is a simpler function if you only need a single check. COUNTIFS, on the other hand, is highly useful when you need to test against multiple conditions.

It is possible to replicate the function of COUNTIFS using multiple AND and OR functions within COUNTIF, but it can be difficult to read and compose. COUNTIFS provides a far simpler way to quickly analyze data with multiple conditionals without nesting multiple levels of IF functions.

It's always important to keep in mind that COUNTIFS will check all the conditionals against the data set. This means that if the data must match only one of a set of conditionals, you should add multiple COUNTIF statements instead.

Comparing and Contrasting Excel’s COUNTIF and COUNTIFS

COUNTIF and COUNTIFS are both excellent tools for pulling meaningful data out of large datasets. They both have a wide array of uses and can be paired with other expressions to extend functionality. Knowing which to use largely comes down to keeping your functions simple and easy to understand.

COUNTIF is ideal in situations where you only need to run a single test against the data. COUNTIFS can make testing more than one facet of data simpler and cleaner. Though both functions serve similar purposes, knowing which to use can help you create cleaner spreadsheets and reports in Excel.