2

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

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)
  • Where can the reference be found regarding the function_code argument? I've learned something today !! Thanks for that. – Jacob Jan Tuinstra Jun 20 '13 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 '13 at 6:15
  • Documentation for SUBTOTAL (including function_code) can be found at Google Docs Help Center. – kenorb Aug 4 '15 at 22:23
  • 1
    @pnuts that is indeed how the OP reads literally. Will edit the answer. – AdamL Sep 29 '17 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

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.