To satisfy the demand of different circumstances you may need to count only the visible rows in Excel. Today we will show you how to count visible rows in Excel. The methods we are going to use will work on all the versions of Microsoft Excel.

Before diving into the big picture, let’s get to know about the workbook that is the base of our today’s examples.

We have a table of three columns; *Books, Genre, Author*. There are 10 books listed here. In reality, real scenario your datasets may be more complex and larger than this.

For showing the count of visible rows we will apply filters on the table. This will hide a few rows and you can understand the differences.

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## Count Visible Rows in Excel

For counting rows you can use the **COUNTA **function. This function will count all the rows that are not empty.

But if you filter your data this function may not be suitable to count only visible rows.

Instead of showing the count of visible rows, this function shows all the rows inside the range.

So, we have understood that mere **COUNTA **function will not give us our desired result. We need some other approaches.

### 1. Count Using Formulas

#### 1.1. Formula using SUBTOTAL Function

We can count visible rows using the **SUBTOTAL** function. Let’s see the syntax of the function first.

`SUBTOTAL(function_number, range)`

**range: **The range you want to subtotal.

**function_number: **This number specifies which calculation should be made. There are several predefined numbers for this placeholder.

Function | Include hidden | Ignore hidden |
---|---|---|

AVERAGE | 1 | 101 |

COUNT | 2 | 102 |

COUNTA | 3 | 103 |

MAX | 4 | 104 |

MIN | 5 | 105 |

PRODUCT | 6 | 106 |

STDEV | 7 | 107 |

STDEVP | 8 | 108 |

SUM | 9 | 109 |

VAR | 10 | 110 |

VARP | 11 | 111 |

These are the functions that can be done using the **SUBTOTAL **function. All you need to do is to insert the number for the function you want to use.

Since our agenda for today is counting rows, we will use the number of **COUNT **functions.

For text values we need to use the **COUNTA **function. So our *function_number *should be 3.

**SUBTOTAL(3,range)**

Write the formula in Excel.

We are showing examples using the *Books* column within our range. You can choose any of the columns.

It gave the rows that are visible.

Now use filter (this will make some rows visible and some invisible)

In our example, 3 rows are visible and our formula is also showing the same.

You can rewrite the formula using *103 *as function_number.

`SUBTOTAL(103,range)`

Write the formula in Excel. And apply filters.

In this example, we have filtered by *Genre *and 5 rows are visible. Our formula gave the correct result.

For showing you how you can make this function work on numbers, we have added an extra column *Price. *

You can use the **COUNTA **function for the numbers as well. But let’s get used to the **COUNT** function.

Generic formula will be something like this

`SUBTOTAL(2,number_range) `

Write the formula in Excel.

For this example, use the *Price *column as a range.

Apply your suitable filter.

You can use *102 *as function_number as well

`SUBTOTAL(102,number_range)`

Write the formula in Excel.

It shows the number of visible rows.

These formulas will work for every filtered data. Feel free to filter or hide manually.

#### 1.2. Formula using AGGREGATE Function

You can also count visible rows using the **AGGREGATE **function as well.

The syntax for the **AGGREGATE **function is as follows

`AGGREGATE(function_number,behavior_options, range)`

**range: **Range you want to aggregate.

**function_number: **This number specifies which calculation should be made.

**behavior_options: **Set this using number. This number denotes how the function will behave.

The **AGGREGATE **function does several tasks so numbers of functions are predefined within it. We are listing few frequently used function’s numbers

Function |
Function_number |
---|---|

AVERAGE | 1 |

COUNT | 2 |

COUNTA | 3 |

MAX | 4 |

MIN | 5 |

PRODUCT | 6 |

SUM | 9 |

LARGE | 14 |

SMALL | 15 |

There are 8 possible values for behavior_options. They are

Value | Behavior |
---|---|

0 | Ignore SUBTOTAL and AGGREGATE functions |

1 | Ignore hidden rows, SUBTOTAL and AGGREGATE functions |

2 | Ignore error values, SUBTOTAL and AGGREGATE functions |

3 | Ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions |

4 | Ignore nothing |

5 | Ignore hidden rows |

6 | Ignore error values |

7 | Ignore hidden rows and error values |

From these tables, you might have seen that for count (numbers or texts) we need to use 3 as function_number.

Since our intention here is to eliminate count for hidden rows, we have several options (4 in particular) for behavior_options place.

Our formula will be

`AGGREGATE(3,1,range)`

*1 stands for ignoring hidden rows, SUBTOTAL and AGGREGATE functions*

Since we gave 3 in *function_number* it will count visible rows only. And along with ignoring hidden rows it will ignore **SUBTOTAL** and **AGGREGATE** functions (if any) as well.

All rows are visible here, so it’s showing 10.

Hide or apply filters to the data set.

We can use 3 in the *behavior_option *as well.

`AGGREGATE(3,3,range)`

*3 will command Excel to ignore hidden rows, error values, SUBTOTAL and AGGREGATE functions. *

Write the formula in Excel.

And apply filters.

Here we have filtered for 3 books and it shows 3 in the *Visible Rows *column.

Use 5 in the behavior_option placeholder

This is also doing the same. Behavior option value 5 stands for ignoring hidden rows.

Earlier options (1 and 3) did the same, but that will take more time since their mechanism is such that they will also evaluate **SUBTOTAL – AGGREGATE** or *Error values*.

Apply different filters and make different numbers of rows visible to test your formula.

Similarly, you can use 7 as the value of the behavior_option parameter.

Value 7 ignores hidden rows and error values.

If you only need to ignore hidden rows it’s better to use 5, since it takes less time to execute and show results.

Filter out values applying a filter on different columns, the formula will give you the result properly.

### 2. Count Using Macros

Using macros you can create your own function in Excel. Here we have a demo function to show you.

```
Function COUNTVISIBLE(range)
Dim xCount As Long
Dim xCell As range
Application.Volatile
For Each xCell In range
If (Not xCell.EntireRow.Hidden) And (Not xCell.EntireColumn.Hidden) Then
xCount = xCount + 1
End If
Next
COUNTVISIBLE = xCount
End Function
```

Explore the *Developer tab* on Excel and click *Visual Basic*

Or simply press **ALT + F11. **

**Microsoft Visual Basic for Applications** window will pop out in front of you.

Click **Insert > Module**, and paste the VBA code to the *Module* window.

Saving the code use this function in Excel.

It counts the rows that are currently visible.

Hide manually or apply a filter to make a few of the rows visible.

It counts only the visible rows.

## Count Visible Rows with Criteria

### 1. Criteria with OFFSET

To count visible rows that match given criteria you need to use a function called **OFFSET. **

Alongside the **OFFSET** function, you need to use the **SUMPRODUCT**, **SUBTOTAL**, **ROW, **and **MIN** functions.

`SUMPRODUCT(SUBTOTAL(3,OFFSET(range,ROW(range)-MIN(ROW(range)),,1)), --(range=keyValue))`

We checked *keyValue *within the *range *and it returns an array of **TRUE **and **FALSE. **The double unary operator `(--)`

coerces the **TRUE** and **FALSE** values into 1’s and 0’s.

Within the **OFFSET **function, we have a couple of **ROW **functions. Subtraction of 2 **ROW **functions provides an array of rows, starting from 0. This array will work as rows inside the **OFFSET **function.

Now we are setting a range, an array of rows, and 1 as height inside the **OFFSET **function. This will provide an array of entire values within the range.

The **SUBTOTAL** function converts the array returned by the **OFFSET** function into an array of 1’s and 0’s where 1s represent visible cells and 0s match hidden cells.

Now, the **SUMPRODUCT **function has two arrays of 1s and 0s. It multiplies the arrays and then calculates the sum.

Write the formula in Excel.

It worked. Make changes in visible rows that match this value and check whether it works or not.

This time we have only one *Thriller *book visible and the formula gives the result correctly.

If our visible rows don’t have the keyword, we are looking for the formula will show 0 as result.

### 2. Criteria with INDIRECT

You can count rows that contain any specific keywords using the **INDIRECT** function as well.

Similar to the earlier formula you need to use the** SUMPRODUCT**, **SUBTOTAL**, **ROW** functions.

Let’s see the generic formula first.

`SUMPRODUCT(SUBTOTAL(103,INDIRECT("column"& ROW(range))),--(range=keyValue))`

We checked *keyValue *within the *range *and it returns an array of **TRUE **and **FALSE. **The double unary operator `(--)`

coerces the **TRUE** and **FALSE** values into 1’s and 0’s.

We have a **ROW **function inside the **INDIRECT **function. We need to set a column name within the **INDIRECT **function.

We have written “B” and inside the **ROW** function given the range **B4:B13**. This will give an array of entire values within these cells.

The **SUBTOTAL** function converts the array returned by the **INDIRECT** function into an array of 1’s and 0’s where 1s represent visible cells and 0s match hidden cells.

Now, the **SUMPRODUCT **function has two arrays of 1s and 0s. It multiplies the arrays and then calculates the sum.

Here our *Search Key *was *Thriller *but no *Thriller *rows were visible, so it gave 0.

Change the *Search Key *and see whether it works fine or not for other values.

It works perfectly. It will also work fine if we use filter further.

## Conclusion

That’s all for today. We have listed several ways to count visible rows in Excel. Hope this will be helpful. Feel free to comment if anything seems difficult to understand. Let us know any other methods to do the task which we have missed here.