How to Count Lines in Excel: Every Method Explained

Counting lines in Excel sounds easy until the real question shows up. Do you want the total number of rows in a range, the number of non-empty rows, rows that match a condition, or only the visible rows after filtering? Those are all different jobs, and Excel uses different formulas for each one.

This guide covers every common Excel row count scenario in one place. You will see the quick formulas, the situations where each method works best, and the mistakes that cause the wrong result. If you just need the short answer, it is here. If you need a durable method for dashboards, reports, filtered tables, or automation, that is here too.

One more distinction matters before you start: in Excel, people often say "lines" when they really mean "rows." This article covers both terms because the search intent is the same. If your data is no longer inside Excel and you want to count pasted text, CSV exports, or plain text files, use the main Line Counter or the dedicated Text to Lines tool instead of forcing spreadsheet formulas onto a text problem.

Quick Answer

=ROWS(A1:A100)

=COUNTA(A:A)

=COUNTIF(A:A,"criteria")

=SUBTOTAL(103,A:A)

Excel Row Count Methods - Quick Comparison

Not sure which Excel row count method to use? This table maps each scenario to the right formula so you can jump straight to the solution.

Click the method name in the second column to jump to the detailed explanation and copy-ready formula.

How to Count All Rows in Excel (Including Blank Rows)

When you need the total number of rows in a range, whether they contain data or not, the simplest Excel row count methods are also the most reliable.

Method 1 - ROWS Function

If you want to count rows in Excel across a fixed range, start with ROWS. It returns the number of rows between the first cell and the last cell in the reference, regardless of whether those rows are blank.

=ROWS(A1:A100)

This returns 100.

=ROWS(A:A)

This returns 1,048,576, which is the maximum row count in a modern Excel worksheet.

=ROWS(A1:A100)-1

This returns 99 when you want to exclude a header row from the total.

Use ROWS when:

• You need a fixed Excel row count for a known range.
• Blank rows should still count.
• You want the cleanest possible formula.

Do not use ROWS when:

• The data range changes every day.
• You only want to count rows with data.

Method 2 - Use the Name Box and Status Bar

If you do not want to use a formula at all, Excel can still give you a quick count.

1. Select the range you want to inspect, for example A1:A100.
2. Look at the left side of the formula area to confirm the selected range.
3. Check the Excel status bar at the bottom of the window for Count.

This method is fast for a quick visual check, especially when someone asks "How many rows are in this selection?" and you do not need a reusable formula.

Method 3 - Use Ctrl+End to Find the Last Row

Another shortcut is to jump to the last used cell in a worksheet and read the row number.

1. Press Ctrl+End.
2. Excel jumps to the last cell it considers used.
3. Note the row number.
4. Subtract header rows if needed.

If Excel lands on row 101 and row 1 is a header, your working data probably spans 100 rows.

How to Count Non-Empty Rows in Excel

Real worksheets usually contain gaps, helper rows, comments, or accidental blanks. If you need to count only rows that contain data, use a non-empty row count formula instead of a raw range count.

Method 1 - COUNTA Function (Recommended)

For most people, the best answer to "how to count lines in Excel with data" is COUNTA.

=COUNTA(A:A)

This counts every non-empty cell in column A.

=COUNTA(A2:A1000)

This counts non-empty cells between A2 and A1000.

COUNTA counts:

• Text
• Numbers
• Dates
• Formulas
• Cells that contain spaces

That last point matters. A cell that looks empty but contains one or more spaces will still be counted by COUNTA.

Method 2 - Exclude Header Rows

If row 1 is a title row, subtract it from the count or start the range below the title.

=COUNTA(A:A)-1

If the first two rows are not data:

=COUNTA(A:A)-2

Or make the range explicit:

=COUNTA(A2:A1048576)

This is usually cleaner because the formula only evaluates rows where data is expected.

Method 3 - SUMPRODUCT for Precise Non-Blank Counts

SUMPRODUCT is more flexible than COUNTA when whitespace, formulas, or multi-column logic get involved.

Count rows that are not empty after trimming spaces:

=SUMPRODUCT((LEN(TRIM(A2:A100))>0)*1)

Count rows where at least one of three columns has data:

=SUMPRODUCT((LEN(TRIM(A2:A100&B2:B100&C2:C100))>0)*1)

Count non-empty cells in one column with explicit boolean logic:

=SUMPRODUCT(--(A2:A100<>""))

Use SUMPRODUCT when your Excel count non empty rows formula needs to ignore whitespace-only cells or combine several columns into one test.

Find the Last Row Number with Data

Sometimes you do not want the row count. You want the last row number that contains data.

=MATCH(2,1/(A:A<>""),1)

Or:

=LOOKUP(2,1/(A1:A1000<>""),ROW(A1:A1000))

These formulas return the last used row number in a column. That is useful when you need a dynamic end point for charts, named ranges, or VBA.

How to Count Rows by Condition in Excel

If your real goal is to count rows that meet a rule, such as rows where the product is Apple or the amount is greater than 100, then COUNTIF, COUNTIFS, and SUMPRODUCT are the core tools.

COUNTIF - Single Condition

COUNTIF handles one condition and is the fastest answer for most simple counting tasks.

=COUNTIF(A:A,"Apple")

Count cells that contain the exact text Apple.

=COUNTIF(A:A,"*Apple*")

Count cells that contain Apple anywhere in the text.

=COUNTIF(A:A,"App*")

Count cells that start with App.

=COUNTIF(B:B,">100")

Count rows where the numeric value is greater than 100.

=COUNTIF(A:A,"<>Apple")

Count cells that are not equal to Apple.

=COUNTIF(A:A,"<>")

Count non-empty cells. This overlaps with COUNTA, but many people prefer it when they are already thinking in terms of criteria.

=COUNTIF(A:A,"")

Count blank cells.

=COUNTIF(B:B,">"&AVERAGE(B:B))

Count values above the average.

=COUNTIF(C:C,"<"&TODAY())

Count dates before today.

Wildcard reference:

COUNTIFS - Multiple Conditions

Use COUNTIFS when you need multiple conditions and all of them must be true.

=COUNTIFS(A:A,"Apple",B:B,">100")

Count rows where column A is Apple and column B is greater than 100.

=COUNTIFS(A:A,"Apple",B:B,">100",C:C,"2024*")

Count rows that match three conditions.

=COUNTIFS(C:C,">="&DATE(2024,1,1),C:C,"<="&DATE(2024,12,31))

Count rows inside a date range.

=COUNTIFS(A:A,"<>",B:B,">0")

Count rows where column A is non-empty and column B is positive.

=COUNTIFS(B:B,">=50",B:B,"<=100")

Count rows where a number falls between 50 and 100.

SUMPRODUCT - Complex Conditions

Use SUMPRODUCT when you need logic that COUNTIF and COUNTIFS do not handle well.

Count rows where column A is Apple or Banana:

=SUMPRODUCT(((A2:A100="Apple")+(A2:A100="Banana"))>0)

Count rows with an exact case-sensitive match:

=SUMPRODUCT((EXACT(A2:A100,"Apple"))*1)

Count unique values:

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

Count rows where either column A is x or column B is y:

=SUMPRODUCT(((A2:A100="x")+(B2:B100="y"))>0)

Method comparison:

How to Count Visible Rows After Filter in Excel

Once a filter is active, COUNTA and COUNTIF still count hidden rows. If you only want the visible rows after filter, use a function built for filtered data.

SUBTOTAL Function (Recommended)

The standard answer is:

=SUBTOTAL(103,A2:A1000)

Here, 103 means COUNTA while ignoring hidden rows.

Common SUBTOTAL codes:

Useful formulas:

=SUBTOTAL(103,A2:A1000)

Count visible non-empty rows.

=SUBTOTAL(102,B2:B1000)

Count visible numeric rows only.

=SUBTOTAL(109,B2:B1000)

Sum visible values to verify that the filter is working the way you expect.

AGGREGATE Function (Excel 2010+)

AGGREGATE is more flexible when you need to ignore hidden rows and errors at the same time.

=AGGREGATE(3,5,A2:A1000)

Here:

• 3 means COUNTA
• 5 means ignore hidden rows
• A2:A1000 is the range

Common option codes:

Use SUBTOTAL for everyday filtered row counts. Use AGGREGATE when errors inside the range would otherwise break the count.

How to Count Rows in an Excel Table

Excel Tables created with Ctrl+T make row counting cleaner because the references expand automatically when new data is added.

Use Structured References

Assume your table is named SalesData.

=ROWS(SalesData)

Count total rows in the table.

=ROWS(SalesData[#Data])

Count the data body only, excluding the header row and total row.

=COUNTA(SalesData[ProductName])

Count non-empty values in one table column.

=COUNTIF(SalesData[Region],"North")

Count rows in the table where Region is North.

Reference parts to remember:

• [#Data] = data only
• [#All] = whole table
• [#Headers] = header row only
• [#Totals] = totals row only

Display a Live Row Count Outside the Table

You can show a dynamic count anywhere else in the sheet.

=ROWS(Table1)

Or build a label:

="Total: "&ROWS(Table1)&" rows"

This is one of the easiest ways to create a self-updating dashboard metric.

How to Count Lines Within a Single Cell in Excel

Sometimes "count lines in Excel" does not mean worksheet rows at all. It means the number of line breaks inside a single cell, usually inserted with Alt+Enter.

Count Line Breaks in a Cell

Excel stores an in-cell line break as CHAR(10). The classic formula is:

=LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1

How it works:

• LEN(A1) counts all characters.
• SUBSTITUTE(A1,CHAR(10),"") removes line breaks.
• The difference between the two lengths equals the number of line breaks.
• Add 1 to convert line breaks into total lines.

If empty cells should return 0 instead of 1, use:

=IF(A1="",0,LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1)

If a cell contains:

Line 1
Line 2
Line 3

The formula returns 3.

Extract a Specific Line from a Cell

Sometimes the next step is not only to count lines in a cell, but to pull out one line.

Extract the first line:

=TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",100)),1,100))

Extract the second line:

=TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",100)),101,100))

This pattern works by replacing each line break with a wide block of spaces, then slicing out the chunk you want.

Count Rows in Excel Using VBA

For repeated reporting, macros can count rows faster than manual formulas and can push the result into messages, cells, or exports automatically.

Basic VBA Row Counts

Count rows in the used range:

Sub CountRows_UsedRange()
    Dim rowCount As Long
    rowCount = ActiveSheet.UsedRange.Rows.Count
    MsgBox "Rows in used range: " & rowCount
End Sub

Find the last non-empty row in column A:

Sub CountRows_NonEmpty()
    Dim lastRow As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    MsgBox "Last row with data: " & lastRow
End Sub

Count rows that match a condition:

Sub CountRows_WithCondition()
    Dim count As Long
    Dim cell As Range

    count = 0

    For Each cell In Range("A2:A1000")
        If cell.Value = "Apple" Then
            count = count + 1
        End If
    Next cell

    MsgBox "Rows with Apple: " & count
End Sub

Reusable VBA Functions

Build small helpers when the same counting task shows up in many workbooks.

Function GetLastRow(ws As Worksheet, col As String) As Long
    GetLastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row
End Function

Function CountNonEmpty(rng As Range) As Long
    CountNonEmpty = WorksheetFunction.CountA(rng)
End Function

Function CountVisibleRows(rng As Range) As Long
    Dim cell As Range
    Dim count As Long

    count = 0

    For Each cell In rng
        If Not cell.EntireRow.Hidden Then
            If cell.Value <> "" Then count = count + 1
        End If
    Next cell

    CountVisibleRows = count
End Function

Write the Row Count into a Cell

If a report needs a live-looking output in the worksheet:

Sub WriteRowCount()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ActiveSheet
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ws.Range("D1").Value = lastRow - 1
    ws.Range("D1").NumberFormat = "0 ""rows"""
End Sub

Count Rows Using Power Query

Power Query is Excel's built-in data transformation layer. It is often the better answer when the worksheet is large, the count is part of a refreshable report, or the data comes from an external source.

Basic Power Query Row Counting

1. Select the source range.
2. Choose Data -> From Table/Range.
3. In Power Query Editor, review the loaded table.
4. Check the row count in the editor status area.
5. Load the result back to Excel if needed.

This is especially useful when you are already cleaning, filtering, or merging data before analysis.

M Code to Count Rows

Count all rows:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    RowCount = Table.RowCount(Source),
    Result = #table({"Row Count"}, {{RowCount}})
in
    Result

Count rows after a condition:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Filtered = Table.SelectRows(Source, each [Region] = "North"),
    RowCount = Table.RowCount(Filtered)
in
    RowCount

When Power Query Is the Right Choice

Use Power Query when:

• The dataset is large.
• The workbook is refreshed on a schedule.
• The source is a CSV, database, or other external feed.
• Row count is part of a wider cleaning workflow.

Do not reach for Power Query when:

• You only need a one-off count.
• A simple Excel formula solves the problem faster.

Count Rows with Dynamic Arrays (Excel 365)

Excel 365 introduced dynamic arrays, which makes row counting more expressive and easier to maintain in modern workbooks.

FILTER + ROWS Combination

Count rows that meet a condition:

=ROWS(FILTER(A2:A100,B2:B100="North"))

Count rows that match two conditions:

=ROWS(FILTER(A2:A100,(B2:B100="North")*(C2:C100>100)))

Handle the case where no rows match:

=IFERROR(ROWS(FILTER(A2:A100,B2:B100="North")),0)

This pattern is excellent for dashboards because the logic is clear: first filter, then count.

UNIQUE + ROWS

Count distinct values in a range:

=ROWS(UNIQUE(A2:A100))

Ignore blanks while counting unique values:

=ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

SEQUENCE

Generate line numbers or row numbers based on a result set:

=SEQUENCE(ROWS(A2:A100))

Or generate row numbers for a filtered result:

=SEQUENCE(ROWS(FILTER(A2:A100,B2:B100="North")))

Common Errors When Counting Rows in Excel

Most bad Excel row counts come from a small set of formula mistakes. Fix these first before assuming the workbook is broken.

#VALUE! Error

A common cause is a bad criteria expression.

Wrong:

=COUNTIF(A:A,>100)

Correct:

=COUNTIF(A:A,">100")

Or, if the threshold is in a cell:

=COUNTIF(A:A,">"&B1)

COUNTA Counts Cells That Look Empty

This happens when cells contain spaces, hidden characters, or formulas that return an empty-looking result.

Diagnose the cell:

=LEN(A1)

If the result is greater than 0, the cell is not truly empty.

More precise count:

=SUMPRODUCT((TRIM(A2:A100)<>"")*1)

SUBTOTAL Does Not Ignore Hidden Rows

If you used:

=SUBTOTAL(3,A2:A100)

You are using the COUNTA code that does not ignore every type of hidden row.

Use:

=SUBTOTAL(103,A2:A100)

The 1xx family is the safer choice when hidden rows are involved.

ROWS Returns 1,048,576

That means you referenced a whole column:

=ROWS(A:A)

If you meant to count your actual data, use a narrower range:

=ROWS(A1:A100)

Or use a dynamic last-row pattern:

=ROWS(A1:INDEX(A:A,MATCH(2,1/(A:A<>""),1)))

Result Includes the Header Row

If your formula starts at row 1, the title row is part of the count.

=COUNTA(A:A)-1

Or start below the header:

=COUNTA(A2:A1048576)

Error lookup:

Count Lines Without Excel - Online Tool

Sometimes Excel is not the right place to count lines at all. If you already copied data from Excel, exported it as CSV, or are working with plain text instead of a worksheet, a browser tool is faster.

Which Excel Row Count Method Should You Use?

Use this decision tree when you need the fastest correct answer:

Need to count rows in Excel?
|- Fixed range, including blanks -> =ROWS(A1:A100)
|- Non-empty rows only -> =COUNTA(A2:A1000)
|- One condition -> =COUNTIF(A:A,"value")
|- Multiple AND conditions -> =COUNTIFS(...)
|- OR logic or case-sensitive logic -> =SUMPRODUCT(...)
|- Visible rows after filter -> =SUBTOTAL(103,A2:A1000)
|- Rows in an Excel Table -> =ROWS(TableName)
|- Lines inside one cell -> =LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1
|- Need automation -> VBA or Power Query
\- Data already copied out of Excel -> linecounter.org

The shortest version is this: use ROWS for fixed ranges, COUNTA for non-empty rows, COUNTIF and COUNTIFS for conditional counts, SUBTOTAL for filtered data, and LEN with SUBSTITUTE when the line count lives inside a single cell. If your Excel data is already outside Excel, stop using formulas and count it directly with the Line Counter.