Count rows automatically in Excel

Avoid the filter to count rows

When you want to count the number of rows corresponding to a specific criteria, many users use the tool Filter.

Filter header

For 1, 2 or 3 different criteria, you can work like that. But if you have to extract a lot of count of rows for many different criteria, the filter is not the good tool.

Purpose of the COUNTIFS function

The COUNTIFS function counts the number of rows corresponding to one or many criteria.

The COUNTIFS has been introduced since Excel 2007 and allows you to add between 1 or 255 criteria.

You can also use the COUNTIF function  but this function only use 1 criteria.

Argument for COUNTIFS

The function COUNTIFS is very easy to build 😎

  • Select the first range of cells where is want to filter
  • Write the value of your criteria
  • and so on for the other criteria...

=COUNTIFS(Range1,Criteria1,Range2,Criteria2,...)

COUNTIF vs COUNTIFS

The difference is just the number of criteria

  • With COUNTIF you can count the rows for one criterion

=COUNTIF(Range1,Criteria1)

  • With COUNTIFS you can count the rows for 256 criteria

=COUNTIFS(Range1,Criteria1,Range2,Criteria2,Range3,...)

The construction is the same, you just add more criteria

Example with 2 criteria

How many single men are in your data-set?

  • Start by writing the formula COUNTIFS

=COUNTIFS(

  • Select the range of cells where you want to search your criterion (the column C)

=COUNTIFS(C2:C20,

  • Enter the value,or the text, you want to search (here "Man").

=COUNTIFS(C2:C20,"Man",

The word "Man" is written between double-quotes because it's a string and all strings must be written between double-quotes in Excel.

Then, for the second criteria

  • Select the range E2:E20
  • Write the value "Single"

=COUNTIFS(C2:C20,"Man",E2:E20,"Single")

Watch your data ranges

If the function COUNTIFS returns #/VALUES!, is because you haven't selected the same range of cells for each criteria.

=COUNTIFS(C2:C20,"Man",E2:E21,"Single")

Replace the criteria with reference

Instead of writing the value of the criteria directly in the formula, you can use the contain of a cell by using the reference of the cell.

=COUNTIFS(C2:C20,H1,E2:E20,"Single")

Let's go deeper

As you can see, it's very easy to count the number of rows with the function COUNTIFS. Now let's see how we can create more sophisticate criteria.😎

Greater than / Less than

You can enhance your criteria by adding logical operator. In other word, you can select all the rows where one criterion is greater than a value.

For example, if we want to determine the number of men with incomes greater than or equal to 50,000. The formula is

=COUNTIFS(C2:C20,"Man",D2:D20,">=50000")

If you use the reference of a cell, your formula is

=COUNTIFS(C2:C20,"Man",D2:D20,">="&H2)

⚠⚠⚠The logical operator must be written between double quotes.

The symbol & is needed to link the logical operator and the cell reference. Without this symbol, the criterion can't be understand.

Select a range of data

If you want to return the number of men who has an income between 50,000 and 70,000, you must write your function with 3 criteria

  • One for the criterion of the man
  • The second for the income greater than 50,000
  • The third, on the same range of cells, for the value lower than 70,000

The formula is

=COUNTIFS(C2:C20,"Man",D2:D20,">=50000",D2:D20,"<=70000")

How to do a OR with COUNTIFS

Now, if you want to count the number of rows for the women single or divorced. Here, you have no other choice than to create 2 formulas. One is for the single women, and the second for the divorced women.

=COUNTIFS(C2:C20,"Woman",E2:E20,"Single")+COUNTIFS(C2:C20,"Woman",E2:E20,"Divorced")

Selection on a part of the criteria

With a computer, you can perform a research on a part of a text string.

  • You can perform a research to replace one character by ? (T?m for Tim or Tom)
  • Or with a * for any number of characters

Most of the time, you will use *. The ? is not really useful

If you want to find all the people with a name starting with a S, you will write the following formula

=COUNTIFS(A2:A20,"S*")

There is 2 person (Samen and Satake)

Now if you want to find all the people with a Y in the name, you will write this formula

=COUNTIFS(A2:A20,"*Y*")

And now, there is 5 people (Champney, Byerly, Gaudefroy, Fordyce, Freyer)

Related posts


Permanent link to this article: https://www.excel-exercise.com/count-rows-automatically-in-excel/


1 comment

    • PARVATHY .V on 19/01/2018 at 05:50
    • Reply

    THIS SIGHT WAS VERY USFULL LIKE ME NOT MORE EDUCATED THANK YOU

Leave a Reply

Your email address will not be published.