How To Sum Visible Rows In Excel

Daily Dose of Excel

Haphazardly Posted Excel Information and Other Stuff

Sum Visible Rows

When you’ve filtered data in Excel, the SUM function still sums cells even if they’re not visible. To SUM only the visible data, you can use the SUBTOTAL function. SUBTOTAL ignores hidden rows and columns.

In this example, there are 2156 rows of data that are filtered so that only those rows whose City is ‘Paris’ are shown.

The formulas below the filtered data are:
C2159 – =SUM(C2:C2157)
D2159 – =SUM(D2:D2157)
C2160 – =SUBTOTAL(9,C2:C2157)
D2160 – =SUBTOTAL(9,D2:D2157)

You can do more than just sum with SUBTOTAL. The first argument (9) is what tells SUBTOTAL to sum. Look up SUBTOTAL_worksheet_function in help to see the other possible arguments.

14 thoughts on “ Sum Visible Rows ”

Interestingly, the subtotal function only works this way with “filtered” lists, and continues to sum rows which are just “hidden”.

That is one of the new features in Excel 2003. They introduced new parameters to fix that issue.

And how do we do this in a Macro?
How do I navigate between the visible rows only?

Thanks in advance,
A.

in excel 2202, i entered the following

a1 = subtotal(9,a:a)
a2 = data autofilter
a3 = data


a99 = data

it worked when i first created it, to automatically
add up the visible data that was filtered by the
autofilter.
now it stopped working and complains about a circular reference. the “subtotal” function is suppose to be smart enough to ignore other subtotals so values are not double counted.
i want the total at the top, so that it is always visible and so that i can add more data at the bottom

anyone have any ideas?

I had this problem with hidden (as distinct from hidden-after-filtering) rows. Microsoft have a solution:
http://support.microsoft.com/?kbid=150363

Thank you! Saved few hours of my time today.

You’ve saved my fingers a lot of pain 🙂

This will work. To sum it up with subtotal and not include hidden rows use a formula like this =SUBTOTAL(109,A2:A5) where 109 tells the subtotal function to sum and ignore hidden rows.

nice tip. saved my time and effort to write vba codes.

What is 9 (9,range)

Great tip. helped me a lot and saved lots of time

For the other function values,

Column 1: Function_num (includes hidden values)
Column 2 : Function_num (ignores hidden values)
Column 3 : Function

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

So, a “9? is a SUM() of all visible and hidden cells in the range and a “109? is a SUM() of only visible cells in the range.

It took me a while to realise that hidden is not the same as filtered. Filtered rows aren’t hidden according to MS. Semantics is obviously not a strong point in the MS camp.

But, I very very rarely manually (or programmatically) hide rows, but I filter all the time.

But strange ….
I try on horizontal way…. =SUBTOTAL(9,H2:AI2)
The formula is still count Hidden cell.

Sum visible (or filtered) range in an Excel Spreadsheet

In Excel, I need to filter and display theCOUNTIF&SUMof both theglobal rangeand thevisible (or filtered) range.

I can already display theCOUNTIF&SUMof theglobal rangewith the following code.

I can also get theCOUNTof thevisible (or filtered) rangeas follows:

However, this still leaves theSUMofvisible (or filtered) rangeoutstanding.

I really stuck. Please, can somebody help me?

6 Answers 6

This will do what you want. Set visibleTotal to the appropriate data type for the total, and change the ws and rng objects to match what you have in your workbook.

In case you only want to sum part of the filtered range (e.g. you filter on column A but want the sum of column B), see this question and answer: Copy/Paste/Calculate Visible Cells from One Column of a Filtered Table.

If one need to COUNT the number of visible items in a filtered list, then use the SUBTOTAL function, which automatically ignores rows that are hidden by a filter.

The SUBTOTAL function can perform calculations like COUNT, SUM, MAX, MIN, AVERAGE, PRODUCT and many more (See the table below). It automatically ignores items that are not visible in a filtered list or table. This makes it ideal for showing how many items are visible in a list, the subtotal of visible rows, etc. It also provide control rows hided manually manually.

The solution to your question would be to count the number of non-blank rows visible in Column A and Column B when a filter is active, use:

Points to remember when you apply SUBTOTAL formula:

  • When function_num (First argument) is between 1-11, SUBTOTAL includes values that are hidden manually but ignore hidden by filter.
  • When function_num is between 101-111, SUBTOTAL excludes all kind of hidden values.
  • In filtered lists, SUBTOTAL always ignores values in hidden rows, regardless of function_num.
  • SUBTOTAL ignores other subtotals that exist in references are ignored to prevent double-counting
  • SUBTOTAL only work with vertical data values arranged vertically.
  • In Horizontal Hidden Columns, values are always included and never ignored.

Excel Sum formula examples – how to total a column, rows or only visible cells

The tutorial explains how to do sum in Excel by using the AutoSum feature, and how to make your own SUM formula to total a column, row or selected range. You will also learn how to sum only visible cells, calculate running total, sum across sheets, and find out why your Excel Sum formula is not working.

If you want a quick sum of certain cells in Excel, you can simply select those cells, and look at the status bar at the bottom right corner of your Excel window:

For something more permanent, use the Excel SUM function. It is very simple and straightforward, so even if you are a beginner in Excel, you will hardly have any difficulty in understanding the following examples.

How to sum in Excel using a simple arithmetic calculation

If you need a quick total of several cells, you can use Microsoft Excel as a mini calculator. Just utilize theplus sign operator(+) like in a normal arithmetic operation of addition. For example:

=A1+C1+D1

However, if you need to sum a few dozen or a few hundred rows, referencing each cell in a formula does not sound like a good idea. In this case, you can use the Excel SUM function specially designed to add a specified set of numbers.

How to use SUM function in Excel

Excel SUM is a math and trig function that adds values. The syntax of the SUM function is as follows:

The first argument is required, other numbers are optional, and you can supply up to 255 numbers in a single formula.

In your Excel SUM formula, each argument can be a positive or negative numeric value, range, or cell reference. For example:

The Excel SUM function is useful when you need to add up values from different ranges, or combine numeric values, cell references and ranges. For example:

The below screenshot shows these and a few more SUM formula examples:

In real-life worksheets, the Excel SUM function is often included in bigger formulas as part of more complex calculations.

For example, you can embed SUM in thevalue_if_trueargument of the IF function to add numbers in columns B, C and D if all three cells in the same row contain values, and show a warning message if any of the cells is blank:

=IF(AND($B2 "", $D2<>""), SUM($B2:$D2), "Value missing")

And here’s another example of using an advanced SUM formula in Excel: VLOOKUP and SUM formula to total all matching values.

How to AutoSum in Excel

If you need to sum one range of numbers, whether a column, row or several adjacent columns or rows, you can let Microsoft Excel write an appropriate SUM formula for you.

Simply select a cell next to the numbers you want to add, clickAutoSumon theHometab, in theEditinggroup, press the Enter key, and you will have a Sum formula inserted automatically:

As you can see in the following screenshot, Excel’s AutoSum feature not only enters a Sum formula, but also selects the most likely range of cells that you’d want to total. Nine times out of ten, Excel gets the range right. If not, you can manually correct the range by simply dragging the cursor through the cells to sum, and then hit the Enter key.

Apart from calculating total, you can use AutoSum to automatically enter AVERAGE, COUNT, MAX, or MIN functions. For more information, please check out the Excel AutoSum tutorial.

How to sum a column in Excel

To sum numbers in a specific column, you can use either the Excel SUM function or AutoSum feature.

For example, to sum values in column B, say in cells B2 to B8, enter the following Excel SUM formula:

=SUM(B2:B8)

Total an entire column with indefinite number of rows

If a column you want to sum has a variable number of rows (i.e. new cells can be added and existing ones can be deleted at any time), you can sum the entire column by supplying a column reference, without specifying a lower or upper bound. For example:

Sum column except header or excluding a few first rows

Usually, supplying a column reference to the Excel Sum formula totals the entire column ignoring the header, as demonstrated in the above screenshot. But in some cases, the header of the column you want to total can actually have a number in it. Or, you may want to exclude the first few rows with numbers that are not relevant to the data you want to sum.

Regrettably, Microsoft Excel does not accept a mixed SUM formula with an explicit lower bound but without an upper bound like =SUM(B2:B), which works fine in Google Sheets. To exclude the first few rows from summation, you can use one of the following workarounds.

    Sum the entire column and then subtract the cells you don’t want to include in the total (cells B1 to B3 in this example):

=SUM(B:B)-SUM(B1:B3)

  • Remembering the worksheet size limits, you can specify the upper bound of your Excel SUM formula based on the maximum number of rows in your Excel version.
  • For example, to sum column B without the header (i.e. excluding cell B1), you can use the following formulas:

    • In Excel 2007, Excel 2010, Excel 2013, and Excel 2016:
      =SUM(B2:B1048576)
    • In Excel 2003 and lower:
      =SUM(B2:B655366)

    How to sum rows in Excel

    Similarly to totaling a column, you can sum a row in Excel by using the SUM function, or have AutoSum to insert the formula for you.

    For example, to add values in cells B2 to D2, use the following formula:

    =SUM(B2:D2)

    How to sum multiple rows in Excel

    To add values ineach row individually, just drag down your Sum formula. The key point is to use relative (without $) or mixed cell references (where the $ sign fixes only the columns). For example:

    =SUM($B2:$D2)

    To total the values in arange containing several rows, simply specify the desired range in the Sum formula. For example:

    =SUM(B2:D6) – sums values in rows 2 to 6.

    =SUM(B2:D3, B5:D6) – sums values in rows 2, 3, 5 and 6.

    How to sum a whole row

    To sum theentire rowwith an indefinite number of columns, supply a whole-row reference to your Excel Sum formula, e.g.:

    Please remember that you shouldn’t enter that ‘Sum of a row’ formula in any cell of the same row to avoid creating a circular reference because this would result in a wrong calculation, if any:

    Tosum rows excluding a certain column(s), total the entire row and then subtract irrelevant columns. For example, to sum row 2 except the first 2 columns, use the following formula:

    Use Excel Total Row to sum data in a table

    If your data is organized in an Excel table, you can benefit from the specialTotal Rowfeature that can quickly sum the data in your table and display totals in the last row.

    A big advantage of using Excel tables is that they auto-expand to include new rows, so any new data you input in a table will be included in your formulas automatically. If can learn about other benefits of Excel tables in this article: 10 most useful features of Excel tables.

    To convert an ordinary range of cells into a table, select it and press Ctrl + T shortcut (or clickTableon theInserttab).

    How to add a total row in Excel tables

    Once your data is arranged in a table, you can insert a total row in this way:

    1. Click anywhere in the table to display theTable Toolswith theDesigntab.
    2. On theDesigntab, in theTable Style Optionsgroup, select theTotal Rowbox:

    Another way to add a total row in Excel is to right click any cell within the table, and then clickTable>Totals Row.

    How to total data in your table

    When the total row appears at the end of the table, Excel does its best to determine how you would like to calculate data in the table.

    In my sample table, the values in column D (rightmost column) are added automatically and the sum is displayed in the Total Row:

    To total values in other columns, simply select a corresponding cell in the total row, click the drop-down list arrow, and selectSum:

    If you want to perform some other calculation, select the corresponding function from the drop-down list such asAverage,Count,Max, Min, etc.

    If the total row automatically displays a total for a column that doesn’t need one, open the dropdown list for that column and selectNone.

    If you want to sum data both in visible and invisible rows, do not add the total row, and use a normal SUM function instead:

    How to sum only filtered (visible) cells in Excel

    Sometimes, for more effective date analysis, you may need to filter or hide some data in your worksheet. A usual Sum formula won’t work in this case because the Excel SUM function adds all values in the specified range including the hidden (filtered out) rows.

    If you want to sum only visible cells in a filtered list, the fastest way is to organize your data in an Excel table, and then turn on the Excel Total Row feature. As demonstrated in the previous example, selecting Sum in a table’s total row inserts the SUBTOTAL function thatignores hidden cells.

    Another way to sum filtered cells in Excel is to apply an AutoFilter to your data manually by clicking theFilterbutton on theDatatab. And then, write a Subtotal formula yourself.

    The SUBTOTAL function has the following syntax:

      Function_num- a number from 1 to 11 or from 101 to 111 that specifies which function to use for the subtotal.

    You can find the full list of functions on support.office.com. For now, we are interested only in the SUM function, which is defined by numbers 9 and 109. Both numbers exclude filtered-out rows. The difference is that 9 includes cells hidden manually (i.e. right-click >Hide), while 109 excludes them.

    So, if you are looking to sum only visible cells, regardless of how exactly irrelevant rows were hidden, then use109in the first argument of your Subtotal formula.

  • Ref1, Ref2, …- cells or ranges that you want to subtotal. The first Ref argument is required, others (up to 254) are optional.
  • In this example, let’s sum visible cells in range B2:B14 by using the following formula:

    =SUBTOTAL(109, B2:B14)

    And now, let’s filter only ‘Banana‘ rows and make sure that our Subtotal formula sums only visible cells:

    How to do a running total (cumulative sum) in Excel

    To calculate a running total in Excel, you write a usual SUM formula with a clever use of absolute and relative cells references.

    For example, I you want to display the cumulative sum of numbers in column B, enter the following formula in B2 and then copy it down to other cells:

    =SUM($B$2:B2)

    You can find the detailed explanation of this basic Cumulative Sum formula and tips on how to improve it in this tutorial: How to calculate running total in Excel.

    How to sum across sheets

    If you have several worksheets with the same layout and the same data type, you can add the values in the same cell or in the same range of cells in different sheets with a single SUM formula.

    A so-called3-D referenceis what does the trick:

    The first formula adds values in cell B6, while the second formula sums the range B2:B5 in all worksheets located between the two boundary sheets that you specify (JanandAprin this example):

    You can find more information about a 3-d reference and the detailed steps to create such formulas in this tutorial: How to create a 3-D reference to calculate multiple sheets.

    Excel conditional sum

    If your task requires adding only those cells that meet a certain condition or a few conditions, you can use the SUMIF or SUMIFS function, respectively.

    For example, the following SUMIF formula adds only those amounts in column B that have "Completed" status in column C:

    =SUMIF(C:C,"completed",B:B )

    To calculate aconditional sumwithmultiple criteria, use the SUMIFS function. In the above example, to get the total of "Completed" orders with the amount over $200, use the following SUMIFS formula:

    =SUMIFS(B:B,C:C,"completed",B:B, ">200" )

    You can find the detailed explanation of the SUMIF and SUMIFS syntax and plenty more formula examples in these tutorials:

    Excel SUM not working – reasons and solutions

    Are you trying to add a few values or total a column in your Excel sheet, but a simple SUM formula doesn’t compute? Well, if the Excel SUM function is not working, it’s most likely because of the following reasons.

    1. #Name error appears instead of the expected result

    It’s the easiest error to fix. In 99 out of 100 cases, the #Name error indicates that the SUM function is misspelled.

    2. Some numbers are not added

    Another common reason for a Sum formula (or Excel AutoSum) not working arenumbers formatted as textvalues. At first sight, they look like normal numbers, but Microsoft Excel perceives them as text strings and leaves them out of calculations.

    One of the visual indicators of text-numbers are the default left alignment and green triangles in top-left corner of the cells, like in the right-hand sheet in the below screenshot:

    To fix this, select all problematic cells, click the warning sign, and then clickConvert to Number.

    If against all expectations that does not work, try other solutions described in: How to fix numbers formatted as text.

    3. Excel SUM function returns 0

    Apart from numbers formatted as text, a circular reference is a common source of problem in Sum formulas, especially when you are trying to total a column in Excel. So, if your numbers are formatted as numbers, but your Excel Sum formula still returns zero, trace and fix the circular references in your sheet (Formulatab >Error Checking>Circular Reference). For the detailed instructions, please see How to find a circular reference in Excel.

    4. Excel Sum formula returns a higher number than expected

    If against all expectations your Sum formula returns a bigger number than it should, remember that the SUM function in Excel adds both visible and invisible (hidden) cells. In this case, use the Subtotal function instead, as demonstrated in How to sum only visible cells in Excel.

    5. Excel SUM formula not updating

    When a SUM formula in Excel continues to show the old total even after you’ve updated the values in the dependent cells, most likely Calculation Mode is set to Manual. To fix this, go to theFormulastab, click the dropdown arrow next toCalculate Options, and clickAutomatic.

    Well, these are the most common reasons for SUM not working in Excel. If none of the above is your case, check out other possible reasons and solutions: Excel formulas not working, not updating, not calculating.

    This is how you use a SUM function in Excel. If you want to have a closer look at the formula examples discussed in this tutorial, you are welcome to download a sample Excel SUM workbook. I thank you for reading and hope to see you on our blog next week.

    SUMIF Visible Cells

    October 27, 2017 – by Bill Jelen

    How to use a SUMIF to sum the *visible* cells that match a criteria. For example, sum all of the records where the Region="East", but ignore the rows that are already hidden. You will be surprised at the function that saves the day.

    Watch Video

    • Question from Jon: Do a SUMIFS that only adds the visible cells
    • Bill’s first try: Pass an array into the AGGREGATE function – but this fails
    • Mike’s awesome solution:
    • SUBTOTAL or AGGREGATE can not accept an array
    • But you can use OFFSET to process an array and send the results to SUBTOTAL.
    • Use SUMPRODUCT to figure out if the row is YES or not.
    • Bill’s second try is like checking to see if the refrigerator light goes out when the door closes:
    • Add a helper column with AGGREGATE of a single cell in each row
    • Point the SUMIFS at that column

    Auto-Generated Transcript

    • hey welcome back it’s time for another
    • dueling Excel podcast I’m Bill Jelen
    • from MrExcel be joined by Mike Irvin
    • from Excel is fun this is our episode
    • 187 some if the visible rose alright
    • welcome back I was doing a seminar in
    • Fort Myers Florida yesterday and John
    • was in the audience and he had a
    • question he said how can you do a sum if
    • it only sums the visible rows okay so
    • right here’s our database and some if
    • it’s simple enough
    • look 385 to 824 see if the answer is yes
    • if it is add up the corresponding cells
    • from b5 to be 24 that all works but then
    • John was applying a filter so we had a
    • category over here and data let’s just
    • use the auto filter to quickly apply the
    • filter so there are all of our B cells
    • and we want to add up just the yes cells
    • that are B cells then that should be
    • something to 100 but this formula is not
    • working so the question is how can we do
    • a sum if that only looks at the visible
    • rows and I said all right well clearly
    • the only thing I know that’s going to
    • ignore visible rows is the aggregate
    • function all right the aggregate
    • function so we’re gonna some number 9
    • comma and then here’s the important part
    • ignore the hidden rows all right and
    • then I just need to find some way to get
    • an array in there and I know the higher
    • versions use an array like everything
    • above 11 for that second argument use an
    • array but you know will an array work
    • here all right so what I wanted to said
    • I was going to do I was gonna say all
    • right we’re gonna take all these numbers
    • here and multiply them times a boolean
    • so look through all of the words over an
    • a 5 equal to yes all right and what’s
    • gonna happen is well these are going to
    • be numbers right and these are going to
    • evaluate in a series of trues and falses
    • and then when we force Excel to multiply
    • the trues and falses times a number the
    • throughs become 1
    • all right so anything it doesn’t say yes
    • is going to evaluate to 0 anything that
    • does that yes is going to evaluate to
    • the sales all right and then we just
    • have to do a ctrl shift enter
    • so my god it doesn’t work I don’t maybe
    • just an enter tell you what Mike I’m
    • gonna keep working on this i’ma throw it
    • for you
    • let’s see what you have thanks MrExcel
    • well you know what aggregate yes that’s
    • one of two functions that can ignore
    • hidden or filtered rows now aggregate
    • was invented in Excel 2010 before
    • aggregate we had the subtotal function
    • and the subtotal function can also
    • ignore hidden rows or filtered rows now
    • the same problem occurs with subtotal as
    • with aggregate if I choose function 9
    • that we’ll just avoid counting filtered
    • rows if I choose 109 that will ignore
    • hidden and filtered rows so 109 I would
    • like to do that but guess what this
    • subtotal function runs into the same
    • problem as the aggregate reference
    • argument means you cannot have an array
    • operation there so what do we do we’re
    • gonna use the offset function to
    • simultaneously get each individual row
    • which subtotal will in essence create
    • individual sub tools and this trick I
    • learned years ago from MrExcels very
    • Oh MrExcel message board now offset it
    • needs a starting point so I’m going to
    • click in the first cell comma and then
    • offset goes down or up a certain number
    • of rows to get a particular value I’m
    • going to say just as an example to see
    • how this function works go down six rows
    • comma columns that’s how many rows you
    • want to subtract our ad we don’t want to
    • subtract or add any so I’m going to
    • leave it blank
    • now offset notice I already have some
    • rows hid in there three rows so offset
    • if I tell it to go down six out of a one
    • two three four
    • five and six so offset should be going
    • and getting that 30 in Selby 16 if I
    • highlight it and hit the f9 key I can
    • see sure enough that it’s doing exactly
    • that
    • control-z but what I really want it to
    • do is simultaneously get every single
    • value so in rows amuse the row function
    • highlight all the way from ten to twenty
    • nine close parenthesis if I highlight
    • this row which is now doing a function
    • argument array operation will just
    • deliver an array of the numbers ten to
    • twenty nine so f9 there we go ah but we
    • really want to tell offset to go down
    • zero one two so it can get offset zero
    • offset one and so on if I can subtract
    • 10 from all of these I’ll have exactly
    • what I want
    • zero one two three and so on control-z
    • well now I simply minus the row of the
    • very first cell in that range close
    • parenthesis now in the rows argument if
    • I f9 there’s an array of all the
    • positions offset needs to offset to get
    • each item individually
    • control-z that is an array in row so it
    • will force offset to deliver exactly one
    • cell for each item in that range and
    • that’ll be different than just the
    • complete range which subtotal cannot
    • handle comma columns we don’t need any
    • so close parenthesis that hole offset if
    • I f9 look at that right now it’s
    • delivering every single cell including
    • right there is three items that are
    • hidden I can see them 30 40 30 but
    • that’s because I haven’t dumped this
    • entire resultant array into subtotal
    • control Z I come to the end closed
    • parenthesis and now when I am nine watch
    • this there we are 0 0 z
    • because subtotal is ignoring the hidden
    • or later we’ll see that it will ignore
    • filtered rows also now I would like to
    • further eliminate some of these numbers
    • for example the second number 50 we
    • can’t have that because it says no right
    • there so I’m going to put this result in
    • array inside of some product and then
    • multiply this array times another array
    • of zeros and ones that will represent
    • yes that’ll be the one no that’ll be the
    • zero
    • control-z now I put it inside of some
    • product that’s array one I need to come
    • to be in comma and highlight the column
    • with the yeses equals and I have the
    • criteria up here now if I click on array
    • 2 + f9 trues and falses but some product
    • can see those trues and falses so we
    • need to convert them to ones and zeros
    • ctrl z so I’m going to use any math
    • operation to convert trues and falses to
    • ones and zeros I’m gonna use double
    • negative because in general that tends
    • to be the fastest and most efficient now
    • I could have taken that array and
    • directly multiplied it by the first
    • array then we would just have that
    • multiplication in array 1 but I’ve
    • chosen to have array number 1 there then
    • some product will multiply times these
    • ones and zeros f9 and we’ll get exactly
    • what we want notice right now 1 1 1
    • means yes yes yes but those will be
    • matched up with the zero zero zero from
    • the subtotal for the hidden rows
    • control-z now I can come to the end
    • close parentheses control enter now I’m
    • hiding over here if I come over and hide
    • right-click hi I should get exactly a
    • total of 50 and 10 control-z now if I
    • turn on the filters with ctrl shift L
    • now I can come up and filter just to
    • show B and there we go
    • 30 times 3 is 90 plus 10 is 100 so there
    • after a great trick I learned years ago
    • from the MrExcel message board
    • subtotal with offset to get our some ifs
    • which is really some product with a
    • criteria here to add only the filtered
    • rows and one last thing about offset
    • remember we were talking about aggregate
    • and subtotal couldn’t handle an array
    • operation right there but we do have
    • some array operations inside of offset
    • offset delivers something that Excel can
    • interpret as a reference it works to
    • disguise the array operation as a
    • reference that in our case subtotal can
    • understand all right I’m gonna throw it
    • back over to MrExcel Oh Mike that is
    • one awesome formula I wonder I couldn’t
    • come up with it there alive at the
    • seminar I was headed in completely the
    • wrong direction but well you’ve been
    • working on that I’m still absolutely
    • convinced that aggregate there’s some
    • way that I can use aggregate to solve
    • this but if I can’t use an array here
    • because this is five and not one of the
    • ones higher than twelve then I still
    • feel like there should be some way to do
    • this and check this out here’s what I’m
    • gonna do is I’m gonna use a helper
    • column and you know as I started think
    • about this one this is this is a lot
    • like finding out if the refrigerator
    • light turns off when you close the door
    • all right you’re not going to know but
    • what I’m gonna do is I’m gonna add it
    • and help her column with an aggregate
    • function and that aggregate function is
    • going to sum ignore the hidden rows and
    • what am I gonna sum I’m gonna sum just
    • this row just this one row and we will
    • copy that down all right now the big
    • question is what is the answer to this
    • when I filter to just the B’s what is
    • the answer
    • are those hidden rows returning zero or
    • not well I don’t know how we can find
    • out well actually I do know how we can
    • find out because we have a secret way to
    • look inside that sound all right so
    • here’s what I’m doing I’m a press
    • control ditto to bring that exact
    • formula down and then instead of
    • pointing to column B I want to point at
    • column C like that
    • right and we’ll press ENTER and then
    • apply a filter all right and let’s see
    • 30 plus 30 plus 30 plus 10 100 it’s
    • working so apparently in those hidden
    • rows the rows we can’t see like for
    • example row 7 here we could do down here
    • equals C 7 aggregate is returning 0
    • because it that hidden row that row
    • itself is hidden all right there you go
    • kind of cheating with that helper column
    • but at least it’s something that I can
    • understand and probably even knock out
    • again without having to look at the
    • formula all right wrap up from today
    • question from John do a summits that
    • only has the visible cells my first pass
    • I was trying to pass an array into the
    • aggregate function but that fails and
    • Mike came up with an awesome solution
    • use the offset function to point each
    • row use the subtotal to see that rows
    • hidden or not and then some product to
    • multiply you know is it yes times the
    • result from the subtotal my second try
    • it’s like checking to see if the
    • refrigerator like goes out once the door
    • closes I had to help her column to each
    • row that says an aggregate of the cell
    • in this row saying ignore any hidden row
    • so that’ll change to 0 on the rock is
    • hidden and then point the sum if that
    • column well there you have it thanks for
    • stopping by we’ll see next time for
    • another dueling excel podcast from mr.
    • Excel and Excel is fun

    Download the sample file here: Duel187.xlsx

    Bill Jelen is the author / co-author of
    Excel Dynamic Arrays Straight to the Point 2nd Edition

    Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.

    How to Sum Only Filtered Data or Visible Cell Values in Excel

    This post will guide you how tosum only visible cell values in Excel. How do I sum only visible cell values in a selected range with a formula in Excel. How to sum only visible rows when filtering data in your worksheet with VBA code in Excel.

    Sum Filtered Data Only with Formula

    Assuming that you have a list of data in range A1:C6, which contain sales s data. And you have filtered out the data that is greater than 5. Then you need to sum all filtered data only or all visible values without hidden values. How to achieve it. You can use a formula based on the SUBTOTAL function to achieve the result. Like this:

    Type this formula into a blank cell and press Enter key to apply this formula. And then all visible value should be summed out.

    Sum Filtered Data Only with VBA Code

    You can also write a User Defined Function with VBA code to achieve the same result. Just do the following steps:

    #1open your excel workbook and then click on “Visual Basic” command underDEVELOPERTab, or just press “ALT+F11” shortcut.

    #2then the “Visual Basic Editor” window will appear.

    #3click “Insert” ->”Module” to create a new module.

    #4paste the below VBA code into the code window. Then clicking “Save” button.

    #5back to the current worksheet, then type the following formula in a blank cell. pressEnterkey.

    =SumFilteredData(C2:C6)

    Video: Sum Filtered Data Only (Sum Visible Cell values Only)

    Comments: