40

As per question title, how to sum only the visible cells on Google Spreadsheets?

1 Answer 1

49

I found the solution, which is to use the SUBTOTAL function with 109 as its first argument.

Here's an example that will sum only the visible values in the B2:B11 interval:

=SUBTOTAL(109,B2:B11)

In German and some other languages, you use a semi-colon instead of a comma:

=SUBTOTAL(109;B2:B11)
4
  • 1
    Note that per the docs, 1) it is a comma that separates cell range from subtotal type, not a semicolon; 2) the prefix "10" is unnecessary, so you could also use =SUBTOTAL(9, B2:B11)
    – Oliver
    Commented Jan 10, 2021 at 2:55
  • 4
    @Oliver In german it is ; because , separates whole numbers from their fractional parts.
    – RedX
    Commented Feb 28, 2021 at 12:34
  • Good to know thx
    – Oliver
    Commented Feb 28, 2021 at 19:32
  • 1
    Cool! I see at support.google.com/docs/answer/3093649 that 103 means COUNTA with hidden values skipped. stackoverflow.com/questions/65642280/…
    – Ryan
    Commented Sep 14, 2021 at 20:01

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