How to Sum Only Visible Cells in Excel (Formula & Examples)

Learn how to sum only visible cells in Excel using the SUBTOTAL function. Explore formulas, examples, filters, and hidden-row calculations.

 

How to Sum Only Visible Cells in Excel (Formula & Examples)

   Learn how to sum only visible cells in Excel using the SUBTOTAL function. Discover how to calculate totals for filtered data, ignore manually hidden rows, and use practical formulas with step-by-step examples.

How to Sum Only Visible Cells in Excel (Formula & Examples)

When working with large Excel spreadsheets, you may often need to filter data to focus on specific records. For example, you might have a sales report containing hundreds of transactions but want to calculate the total sales for only the rows currently visible after applying a filter.

A normal SUM formula may not give you the result you expect because it includes values from hidden rows. This can make your calculations inaccurate, especially when preparing filtered reports, monthly summaries, or business performance sheets.

Fortunately, Excel provides a simple solution. You can use the SUBTOTAL function to add only visible cells while ignoring filtered-out rows.

In this guide, you'll learn how to sum visible cells in Excel using practical examples, understand the difference between SUM and SUBTOTAL, and discover alternative methods for handling hidden rows.

What Does Sum Only Visible Cells Mean in Excel?

Summing only visible cells means calculating the total of values that are currently displayed in your worksheet while excluding rows hidden by filters or manual hiding.

For example, imagine you have a sales report containing transactions from January, February, and March. If you apply a filter to display only February transactions, you may want the total sales for February without including the other months.

This is where the SUBTOTAL function becomes useful.

Unlike the regular SUM function, SUBTOTAL can ignore filtered-out rows and, depending on the function number you choose, manually hidden rows as well.

SUM vs SUBTOTAL: What's the Difference?

Before learning the formulas, it's important to understand how SUM and SUBTOTAL behave.

Feature

SUM Function

SUBTOTAL Function

Adds numerical values

Yes

Yes

Ignores filtered rows

No

Yes

Ignores manually hidden rows

No

Depends on function number

Updates when filters change

No

Yes

Supports different calculations

No

Yes

Best for visible-cell totals

No

Yes

If your goal is to calculate totals based on filtered data, SUBTOTAL is generally the better choice.

How to Sum Only Visible Cells Using SUBTOTAL

The easiest way to add visible cells in Excel is by using the SUBTOTAL function.

The formula is:

=SUBTOTAL(9,B2:B10)

Here's what each part means:

  • SUBTOTAL: Excel function used to perform calculations on a range of data.

  • 9: Tells Excel to perform a SUM calculation while ignoring filtered-out rows.

  • B2:B10: The range containing the numbers you want to add.

For example, suppose you have the following sales data:

Product

Sales

Laptop

15,000

Mobile

12,000

Keyboard

2,000

Monitor

8,000

Mouse

1,000

Printer

6,000

If you enter the formula below:

=SUBTOTAL(9,B2:B7)

Excel calculates the total of all visible sales records.

Example: All rows are visible

Total visible sales

₹44,000

15,000 + 12,000 + 2,000 + 8,000 + 1,000 + 6,000

Now, if you apply a filter and display only Laptop, Mobile, and Monitor, Excel automatically recalculates the result.

Example: After applying a filter

Total visible sales

₹35,000

15,000 + 12,000 + 8,000

You don't need to change the formula whenever you apply a different filter. Excel updates the total automatically.

How to Sum Visible Cells While Ignoring Manually Hidden Rows

Sometimes, you may hide rows manually instead of using Excel's filter feature.

In this situation, the function number you choose makes a difference.

Excel provides two useful options:

Formula

Behaviour

=SUBTOTAL(9,B2:B10)

Ignores filtered rows but includes manually hidden rows

=SUBTOTAL(109,B2:B10)

Ignores both filtered and manually hidden rows

If you want to calculate the total of only the cells visible on your screen, use:

=SUBTOTAL(109,B2:B10)

This formula is particularly useful when preparing reports where you manually hide certain rows to simplify the presentation.

Practical example:

Suppose your monthly expenses are:

Expense

Amount

Rent

10,000

Electricity

2,000

Internet

1,000

Transport

3,000

Office Supplies

2,000

The total is ₹18,000.

If you manually hide the Internet and Office Supplies rows, the formula:

=SUBTOTAL(109,B2:B6)

returns ₹15,000.

It excludes both manually hidden rows from the calculation.

How to Sum Visible Cells After Applying Filters

If you're working with a large dataset, Excel's Filter feature makes it easy to calculate totals for selected records.

Follow these steps:

Step 1: Select your dataset, including the column headings.

Step 2: Go to the Data tab in the Excel ribbon.

Step 3: Click Filter to enable dropdown arrows in your column headings.

Step 4: Open the filter dropdown for the column you want to filter.

Step 5: Select the records you want to display and click OK.

Step 6: Enter the following formula below your data:

=SUBTOTAL(9,B2:B100)

Excel will calculate the total using only the visible rows. 

How to Sum Only Visible Cells in Excel (Formula & Examples)

You can change the filter criteria whenever needed, and the subtotal will adjust automatically.

How to Use SUBTOTAL with an Excel Table

If you regularly work with sales reports, expense trackers, or inventory lists, converting your data into an Excel Table can make calculations easier.

Here's how:

  • Select your dataset.

  • Press Ctrl + T on Windows or Command + T on Mac.

  • Confirm that your table has headers.

  • Click OK.

  • Select any cell inside the table.

  • Open the Table Design tab.

  • Enable the Total Row option.

Excel adds a total row at the bottom of your table.

Click the dropdown in the total cell and select Sum. Excel automatically uses the SUBTOTAL function to calculate the visible total.

For a table named SalesTable, you can also use a structured reference:

=SUBTOTAL(109,SalesTable[Sales])

This makes your formula easier to understand because it refers to the Sales column by name instead of using cell references. 

How to Sum Only Visible Cells in Excel (Formula & Examples)

Common Mistakes When Summing Visible Cells in Excel

Even though SUBTOTAL is straightforward, a few mistakes can lead to unexpected results.

Using SUM instead of SUBTOTAL

The SUM function includes values from hidden rows. If you want filter-aware calculations, use SUBTOTAL.

Choosing the wrong function number

Function number 9 ignores filtered rows but includes manually hidden rows. Use 109 when you want to exclude both.

Including the formula cell in the calculation range

Avoid placing your subtotal formula inside the range it calculates. Doing so can create unnecessary calculation issues. Place the formula outside the data range.

Expecting SUBTOTAL to ignore every type of hidden data

SUBTOTAL ignores rows hidden by filters. It also ignores manually hidden rows when using function numbers 101–111. However, it does not ignore individual hidden columns or cells.

Using the wrong data range

Always check that your formula includes the correct column and all relevant rows. Missing even a few cells can affect your final total.

Other Useful SUBTOTAL Function Numbers in Excel

The SUBTOTAL function is not limited to addition. It can also calculate averages, counts, maximum values, and minimum values.

Here are some commonly used function numbers:

Function Number

Calculation

1

AVERAGE

2

COUNT

3

COUNTA

4

MAX

5

MIN

9

SUM

101

AVERAGE (ignores manually hidden rows)

102

COUNT (ignores manually hidden rows)

103

COUNTA (ignores manually hidden rows)

104

MAX (ignores manually hidden rows)

105

MIN (ignores manually hidden rows)

109

SUM (ignores manually hidden rows)

These options make SUBTOTAL useful for more than just calculating totals.

SUMPRODUCT Alternative for More Advanced Calculations

For more complex spreadsheets, you may sometimes need to calculate visible values using additional conditions.

The SUBTOTAL function is usually the simplest choice for standard filtered totals. However, SUMPRODUCT can be combined with SUBTOTAL and OFFSET to create more advanced calculations.

For example:

=SUMPRODUCT(SUBTOTAL(109,OFFSET(B2,ROW(B2:B10)-ROW(B2),0,1)))

This formula evaluates the visibility of individual rows and adds their corresponding values.

It can be useful when building advanced reports, but it is more complicated than a standard SUBTOTAL formula.

For everyday spreadsheets, start with SUBTOTAL unless you have a specific requirement that needs a more advanced formula.

Frequently Asked Questions (FAQs)

Can Excel SUM only visible cells?

Yes. Use =SUBTOTAL(9,B2:B10) to add visible cells while ignoring rows hidden by filters.

Which formula ignores manually hidden rows in Excel?

Use =SUBTOTAL(109,B2:B10). It ignores both filtered-out and manually hidden rows.

Why does SUM include hidden rows?

The SUM function adds all numerical values in its selected range, regardless of whether their rows are hidden or filtered.

Does SUBTOTAL update automatically when I change filters?

Yes. Excel recalculates the result when you change the filter criteria, showing the total for the currently visible rows.

Can I use SUBTOTAL in an Excel Table?

Yes. You can enable the Total Row option in Table Design or use a structured reference such as =SUBTOTAL(109,SalesTable[Sales]).

Final Thoughts

Summing only visible cells in Excel is much easier when you use the SUBTOTAL function instead of SUM.

For filtered datasets, =SUBTOTAL(9,range) is a simple and reliable option. If you also want to exclude manually hidden rows, use =SUBTOTAL(109,range).

Whether you're managing sales figures, tracking monthly expenses, preparing inventory reports, or analysing business data, these formulas can save time and help you avoid incorrect totals.

The best approach is to use SUBTOTAL for filtered reports and Excel Tables when you regularly work with changing datasets.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content