Counting functions in Excel: COUNT, COUNTA, COUNTIF, COUNTIFS



How to work with Countif with multiple criteria in Excel?

Counting in Excel is one of the most commonly used functions. Most business requirements require data counting. For example, if you run a business, you might need to know how many people use a particular brand of shampoo. To calculate this data, you need to use a counting formula such as COUNT, COUNTIF, COUNTIFS, COUNTA, etc. You can also use a pivot table for counting, but learning the pivot table requires some effort, whereas the above formulas are quite simple ...

But what about using COUNTIF and multiple function conditions (with multiple criteria) in Excel? Let's see how to deal with this.

Countif (COUNTIF)

COUNTIF is used to count the number of cells in a range based on certain criteria. Basic syntax for COUNTIF:

 = COUNTIF (range, criteria)

In the range box, you need to specify the range in which you want to count the number of cells. In the criteria field, you must specify the criteria. The criteria can be numbers, strings, cell references, or expressions. Based on the specified criteria, the COUNTIF function examines each cell in the range and counts the cells containing the criteria.

For example, suppose you want to count the number of people who receive a salary of more than 50,000 rubles. You can use the COUNTIF formula in a range. COUNTIFS function in Excel

As you can see in the above image, we have the employee data in the table, and in the second column, we have the corresponding salary of each employee. If you need to count the number of employees earning more than $ 5,000, you can use the COUNTIF formula by specifying the salary column in the range field and > 5000 in the criteria field. The COUNTIF function will count the number of matching cells.

Using COUNTIF

Count the number of cells containing only a row.

If you need to count the number of cells in a range containing a string (not numbers, dates, or times), you can use the COUNTIF function with criteria marked with an asterisk (*). Syntax:

 = COUNTIF (range, *)

Count the number of nonblank cells

If you want to count the number of cells containing text, dates or numbers, you can use the formula:

 = COUNTIF (range,)

This formula counts all nonblank cells.

Count the number of cells whose values ​​are greater or less than a certain value.

You can use the greater than symbol in the criterion field to count cells that exceed a certain specified value. For example:

COUNTIF (range; "> 10")

To count cells whose values ​​are less than a certain value, you can use the less than symbol in the criterion field. For example:

COUNTIF (range; "<10")

Count the number of cells whose values ​​are equal or not equal to some value.

You can use the equal symbol in the criterion field to count cells that are equal to a number. For example:

COUNTIF (range; "= 10")

You can use the not equal symbol in the criterion field to count cells whose values ​​are not equal to a number. For example:

COUNTIF (range; "10")

COUNTIF with multiple criteria

If there is more than one range and criteria, you can use the COUNTIFS function. It works the same as COUNTIF, but it is used with several criteria.

The syntax is COUNTIFS:

 = COUNTIFS (range 1, criterion 1, range 2, criterion 2 ..)

You can select multiple ranges and apply criteria. The final quantity will be displayed based on various criteria.

Continuing with our previous example, suppose we want to define a certain growth in a corporation. Suppose that the criterion for the promotion is as follows: the salary must be less than 50,000 rubles, and the attendance of the employee must be more than 85%.

In the above example, we have the employee's name as the data in the first column, the salary data in the second column, and the attendance data in the third column. Now we are counting the number of employees with a salary of less than 50,000 rubles. and more than 85% attendance. Since the data is referred to in two different columns, we must specify the appropriate range and apply the criteria. This will give us the number of cells that meet all the criteria.

It's important to know!

By default, COUNTIFS applies “and” logic to various specified criteria.

COUNTIFS returns the number of rows whose cells match the specified criteria.

COUNTIF and COUNTIF will also work if the cells are not contiguous.

You can also use special characters like *, & etc., depending on your requirement.

There is no shortcut for this function. You must enter a formula starting with an equal sign, enter ranges and criteria, and press enter.

(OR) Criteria in COUNTIF

Since COUNTIF uses the default and logic, you will need to use a different method to perform the OR operation in COUNTIF. In the above example, if you want to count the number of employees who either have a salary of less than $ 5,000 or an attendance greater than 85%, we need to apply the OR logic here.

Basic syntax for OR logic in COUNTIF:

 = COUNTIF (range_1, criterion_1) + COUNTIF (range_2, criterion_2)

In this example, we are working with the same data that was already used as the COUNTIFS example above. But here we are using OR logic instead of AND (the COUNTIF output with AND logic is 2 by default; and the COUNTIF output with OR logic is 9).

Here we add two different COUNTIF functions to handle multiple criteria. The result of the first COUNTIF (where the criterion is a salary of less than $ 5,000) is 2, and the result of the second COUNTIF (where the criterion is attendance above 85%) is 7. Thus, we can achieve the logic of OR in COUNTIF. And note that in order to get the correct answer in this case, we need to subtract the number of people belonging to both categories.

While there are many applications for COUNTIF and COUNTIFS, these functions are easy to learn and remember. I have provided some examples that you can try directly in Microsoft Excel. After completing these examples, you can create your own example by collecting random data from the Internet. Once you understand how COUNTIF works with multiple criteria, and enough practice, you can count cells that contain any data!

How to consider multiple criteria?

Excel formula for counting multiple criteria: = COUNTIFS. The s at the end makes it plural and therefore implies that there are multiple criteria (2 or more).

In the examples and illustrations below in this tutorial, you will learn exactly how to use COUNTIFS in Excel.

How do I use multiple Countif criteria?

COUNTIFS works the same as Countif, except that you can add additional criteria, separated by commas. Here's a step-by-step guide on how to do it.

  • Step 1: Document the criteria or conditions that you want to test.
  • Step 2: Enter = COUNTIFS ( and select the range in which you want to test the first criterion).
  • Step 3: Enter the test for the criteria.
  • Step 4: Select the second counting range that you want to test (it can be the same range again or a new one).
  • Step 5: Enter the test for the criteria.
  • Step 6: Repeat the above rule as many times as necessary.
  • Step 7: Close the bracket and press Enter.

Example of a counter with multiple criteria

As you will see, each of the criteria is tested, and Excel calculates how many times the test result is correct. This is how to use Excel to count multiple criteria.

Why use Countif?

There are many cases where you might want to count how many times multiple criteria have been met. In finance, this can be used to select companies that match a specific profile. As in the case above, we wanted to know how many companies exceed a certain market cap or other economic parameters. Using OR logic in COUNTIF / COUNTIFS can make it more efficient and save you the trouble of writing long formulas.






Comments (0)

Leave a comment