Google-sheets – Google Spreadsheets sum only shown rows

formulasgoogle sheets

Is it possible to sum a range of cells while ignoring rows which are not hidden?

Best Answer

I'm not sure if you necessarily require a Google Apps Script solution; the tags you have used suggest that a spreadsheet formula might be adequate. If this is the case, then it is definitely possible to achieve this using SUBTOTAL.

For example:

=SUBTOTAL(109,A:A)

will sum all cells in column A that are not hidden. 109 is the function_code which consists of 9 for SUM, prepended with 10 so hidden values will be skipped.

Your question requests that the sum should be "ignoring rows that are not hidden", which, I believe, is equivalent to "sum the hidden rows". This may be achieved using:

=SUM(A:A)-SUBTOTAL(109,A:A)

Related Solutions

Google-sheets – Sum of all cells in Google Spreadsheet where matching row fits a value

=SUMIF(D:D,"bob",C:C)

in C column are numbers, in D column are ("bob" or others)

if you wish do not sum all column you can use for example

=SUMIF(D2:D6,"bob",C2:C6)

Note: Text is case sensitive (Bob is not equal bob)

Google-sheets – SUM of all cells in another column, except above rows

This is almost the same as a cummulative sum, but reversed.

Formula

=SUM(B2:$B$7)

=ARRAYFORMULA(SUMIF(ROW(A2:A7);">="&ROW(A2:A7);B2:B7))

Explained

If you add the first formula in C2, then you're able to copy that cell (drag) to C7. The ARRAYFORMULA will present the result at once.

Example

I've created an example file for you: Inverse Cummulative Sum

Reference

Cummulative Sum