Excel Group Rows Count: How to Count Rows in Groups

How-to basics · Updated November 19, 2026

Excel’s Group command does not calculate a row count; it only lets you collapse or expand selected rows. To count rows, use ROWS for the size of a fixed range, COUNTA for populated records, or SUBTOTAL(103,…) to count nonblank records that remain visible after filtering or collapsing rows.

What “group rows count” can mean

The phrase can refer to two different tasks. You might want to group rows with Excel’s outline controls and find out how many rows are in one of those groups. Or you might want a count of records for each category, such as the number of orders for each region. These are related, but Excel handles them differently.

Row grouping is a display feature: it adds outline controls so you can hide or reveal detail. It does not label the group with a count, and it does not combine rows that share a category. Counting records is a calculation. The right method depends on whether you need the physical size of a selected block, the number of filled records, the number currently visible, or totals by category.

How to group rows in Excel

In Excel for Microsoft 365 on Windows, select the rows using their numbered row headers, then choose Data > Outline > Group. If Excel asks whether to group rows or columns, choose Rows. A bracket and outline buttons appear beside the row numbers; use the minus button to collapse the group and the plus button to expand it. Nested groups can create more than one outline level.

Grouping does not delete or rearrange the rows. It changes which rows are displayed, so it is useful for hiding detail beneath a summary or organizing a worksheet into sections. It is not the same as sorting, filtering, or grouping records by matching values. To remove an outline, select the grouped rows and use Data > Outline > Ungroup. On Mac and Excel for the web, ribbon labels or their placement may differ, but look for the Group or Ungroup commands on the Data tab.

Count the rows in a fixed group

If you know the exact range that makes up a group, ROWS returns the number of rows in that range, whether cells are filled or blank. For example, if the group occupies rows 2 through 6, the range has five rows. With the group occupying B2:B6, enter =ROWS(B2:B6); the result is 5. The column does not matter here because ROWS counts the range’s row positions, not its contents.

Use this approach when the question is “How many worksheet rows are in this block?” It remains 5 if one of those rows is empty. That can be useful when a group represents a fixed-sized template, but it can overstate the number of actual records when the range includes blank lines.

If you need the count to adjust when the group’s boundaries change, consider putting the records in an Excel table and using its structured data, or maintain a clear range that expands with the group. A formula that refers to B2:B6 will not automatically include a new row inserted outside that range.

Count populated records rather than row positions

When each record has a reliably filled field, COUNTA is usually a better count than ROWS. For example, suppose every order has an order ID in column B, and a particular group covers B2:B6. Enter =COUNTA(B2:B6); if B2:B6 contains five order IDs, the result is 5. If one cell in that range is blank, the result is 4.

Choose a column that is filled once per record, such as an ID or required date. Counting a descriptive field can miss records if that field is legitimately blank. Also note that COUNTA counts cells containing formulas even when those formulas display an empty string, so a formula-filled column may not behave like a visually populated-only count.

COUNT is different: it counts numeric values, not all nonblank entries. If your anchor field contains text IDs, COUNTA is the appropriate function. If you need to count only numeric entries, use COUNT instead. Neither function automatically knows which worksheet rows form a visual outline group; you must give it the group’s range.

Count only rows that are visible

Filtering or collapsing an outline can hide rows without removing them. If you want a count that responds to those hidden rows, use SUBTOTAL with function number 103, which applies COUNTA behavior while excluding filtered-out and manually hidden rows. With one nonblank order ID in B2:B8 for each record, enter =SUBTOTAL(103,B2:B8). If all seven IDs are visible, the result is 7; if two are hidden by a filter or by collapsing the outline, the result is 5.

The range must contain a nonblank cell for every record you want counted. If some IDs are blank, SUBTOTAL(103,…) will not count those rows. It counts nonblank cells, not row positions. Also, SUBTOTAL excludes nested SUBTOTAL results, which helps avoid double-counting when a worksheet contains subtotals.

For a count of visible numeric values rather than visible nonblank values, use function number 102. Function numbers 1–11 include manually hidden rows but still exclude rows removed by filtering; numbers 101–111 exclude manually hidden rows as well. Outline-collapsed rows are hidden, so 103 is generally the useful choice when counting visible, populated records in a collapsed group.

Count records by category

If “group” means a category such as region, department, or product type, count matching labels rather than using outline controls. Suppose A2:A8 contains East, East, West, East, West, North, and East. To count East records, enter =COUNTIF(A2:A8,"East"); the result is 4. COUNTIF counts cells that meet one condition, so it is a simple way to make a category count beside a list.

For a list of category names in D2:D4, you can place a count formula beside each name and copy it down. With source categories in A2:A8 and the category to count in D2, enter =COUNTIF($A$2:$A$8,D2) in E2, then fill down. The formula returns the count for the category named in D2; the dollar signs keep the source range fixed as the formula is copied.

For several conditions, use COUNTIFS. For example, you could count orders for East in a specific status column as well as a region column. Make sure each criteria range has the same dimensions. These formulas produce counts by matching data values; they do not require the rows to be sorted or manually grouped.

Choose between formulas and a PivotTable

A formula is convenient when you need a count in a particular cell, want the result to update as data changes, or are counting one or a few known groups. ROWS answers a range-size question, COUNTA answers a populated-cell question, SUBTOTAL answers a visible-record question, and COUNTIF or COUNTIFS answers a criteria question.

A PivotTable is often easier when you need counts for many categories at once. Select a cell in the dataset, choose Insert > PivotTable, and place the category field in Rows and a field that is populated for each record in Values. If Excel summarizes that Values field by Sum instead of Count, open its field settings and select Count. The PivotTable can display one count per category and can be refreshed after source data changes.

Use outline grouping when the goal is to hide or reveal detail, not to summarize categories. You can use a PivotTable or formulas for the count and still keep an outline for navigation, but they solve separate problems. Sorting or filtering a table by category can make records easier to inspect; it does not itself create a count for each category.

Common counting problems

  • The result is one too high: Check whether the selected range includes a header, a subtotal row, or an extra blank row. ROWS counts every row position in its range.
  • The result is too low: If using COUNTA or SUBTOTAL(103,…), check that the chosen anchor column is filled in for every record. Blank anchor cells are not counted.
  • The count does not change when rows are hidden: ROWS and COUNTA include hidden rows. Use SUBTOTAL(103,…) when the count should reflect visible nonblank records.
  • COUNTIF misses a category: Check spelling, extra spaces, and the actual values in the source cells. COUNTIF is not a substitute for counting a manually selected outline group.
  • New records are excluded: A formula with a fixed range may not include rows added beyond its endpoints. Expand the range or use an Excel table so references can include new records more conveniently.

Frequently Asked Questions

How do I count rows in a collapsed group?

Use SUBTOTAL(103,…) on a column that has a nonblank value for every record in the group. It counts visible nonblank cells, so rows hidden by the collapsed outline are excluded. If you want the group’s full row capacity regardless of visibility, use ROWS on its range instead.

Does Excel automatically show a count when I group rows?

No. The Group command adds outline controls for expanding and collapsing rows; it does not calculate or display a count. Add a formula, use a PivotTable, or select the range and check Excel’s status bar for a quick selection count.

How can I count blank rows in a group?

ROWS counts all row positions, including blank ones, when the group is a continuous range. If you need to count blank cells in one particular column, use COUNTBLANK on that column’s range. Be clear whether you mean entirely empty worksheet rows or blank cells in a chosen field; those are not always the same.

How do I count each category without grouping rows?

Use COUNTIF for a single criterion or COUNTIFS for multiple criteria, or create a PivotTable with the category field in Rows and a populated record field in Values set to Count. Sorting the source list is optional for these methods.

Why does my count change after I filter the worksheet?

Ordinary functions such as ROWS, COUNTA, and COUNTIF generally still include records hidden by a filter. SUBTOTAL is designed to exclude filtered-out rows, so use it when the desired result is a count of the currently visible records.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.