How to sum only visible cells on Google Sheets?

Question

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

Simon East · Accepted Answer · 2022-12-19 18:42:16Z

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)

Simon East

4453 gold badges6 silver badges17 bronze badges

answered Jun 22, 2017 at 13:55

Weslei

1,3153 gold badges11 silver badges17 bronze badges

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
Jan 10, 2021 at 2:55
4

@Oliver In german it is ; because , separates whole numbers from their fractional parts.
– RedX
Feb 28, 2021 at 12:34
Good to know thx
– Oliver
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
Sep 14, 2021 at 20:01

Add a comment |

1 Answer 1