2

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

1

2 Answers 2

3

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)
4
  • Where can the reference be found regarding the function_code argument? I've learned something today !! Thanks for that. Jun 20, 2013 at 5:55
  • 2
    Unfortunately the Google Docs documentation leaves something to be desired (the links within that blog post used to work). In this case I think it would be reasonably safe to rely on the Excel documentation: office.microsoft.com/en-au/excel-help/…
    – AdamL
    Jun 20, 2013 at 6:15
  • Documentation for SUBTOTAL (including function_code) can be found at Google Docs Help Center.
    – kenorb
    Aug 4, 2015 at 22:23
  • 1
    @pnuts that is indeed how the OP reads literally. Will edit the answer.
    – AdamL
    Sep 29, 2017 at 10:47
0

Turns out its not possible (for now).

Feature request: https://code.google.com/p/google-apps-script-issues/issues/detail?id=195

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.