Count specific rows in excel

In this article, we will learn about how to count specific row in excel. 

To count values having conditions, use the COUNTIFS function. We can count the rows having multiple conditions. So let's see the syntax for the COUNTIFS function in excel.

Excel provides COUNTIFS function that counts on multiple conditions.

Syntax:

= COUNTIFS(range1, criteria1, [range2], [criteria2],...)

Range1 : first range for criteria1.
Criteria1 : criteria from range1.
Range2 : range for criteria2. Optional.
Criteria1 : criteria from range2. Optional.

Let's make a formula using the above functions:
Firstly, we need to generalize the formula for all users who can use it for their purposes.
Syntax:

= COUNTIFS (A:A , value1, B:B, value2, C:C, value3,...)

The above matches all values in column A having value1 and column having value2 and column C having value3 and so on. Then it counts all the rows having all value1, value2, value 3 and so on.
This all might be confusing. So Let’s use the formula in an example to get its more understanding.
Here we have table_array and We need to find the count the values in the list having 2 conditions:

  1. Emp : Joe 
  2. Region : North


We need to find the row count using the below formula
Use the formula:

= COUNTIFS ( A:A, F4, B:B, F5)

Explanation:
The function matches value in F4 ( Joe ) with column A. And then matches the value in F5 ( North ) with column B.
So the function returns the count of rows having both conditions.

As you can see the formula returns the value for two given conditions.

Notes:

  1. The function returns an error if the address returned by the function is not valid.
  2. The function returns 0 if the cell value is blank.
  3. Use the INDEX function as INDIRECT function is a volatile function.
  4. Use wildcards with the function to get the more fine results. 
  5. With numarice criterias you don’t need to use double quotes. However while working with logical operators (<,>,=) you need to use double quotes.
  6. Each range should have same number of columns and rows.

That's all about, how to Count specific rows in excel. Explore more articles on Excel Formulas here. Please feel free to state your query or feedback for the above article.

Related Articles

Count cells between dates

Count Cells That Contain This Or That

Count cells if less than value

Count table rows & columns in Excel

How to use the COUNTIFS function in excel

Popular Articles

Edit a dropdown list

If with conditional formatting

If with wildcards

Vlookup by date

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube