79

I have the following data in a Google Spreadsheet:

+------+---------+
| Time | Include |
+------+---------+
| 0:30 | No      |
| 1:00 | Yes     |
| 0:30 | Yes     |
| 0:30 | No      |
| 0:30 | Yes     |
+------+---------+

I would like to add up the time where the column to the right is "Yes".

I am using the following to add up all time (3:00):

=SUM(A1:A5)

and I've tried the following to add up the time (2:00) where the adjacent column is Yes, but Google reports "#ERROR!":

=SUM(FILTER(A1:A5;B1:B5="Yes"))
Improve this question
3
  • 1
    If you'd searched here on Web Applications, then you would've found a few examples on how to do a summation. Another way of doing it is by using the SUMIF formula: =SUMIF(B2:B6;"Yes";A2:A6)
    – Jacob Jan
    Commented May 3, 2013 at 11:30
  • The reason to why you need a comma in stead of a semicolon, has to do with the time-zone your in. Europe uses a comma as where USA uses a semicolon.
    – Jacob Jan
    Commented May 3, 2013 at 11:37
  • 1
    @JacobJanTuinstra: Whether you can use a comma or semicolon is not necessarily related to the timezone, but with the locale/_language_ settings. Specifically whether the comma is being used as a decimal separator. If it is then you can't use a comma to also separate function arguments. In English we generally use a period as a decimal separator, so we can use either.
    – MrWhite
    Commented May 3, 2013 at 16:11

2 Answers 2

Reset to default
92

Just realised FILTER should be comma separated:

=SUM(FILTER(A1:A5,B1:B5="Yes"))
Improve this answer
6
  • 7
    Note that whether you need to use a comma or semicolon to separate function arguments is dependent on your language settings.
    – MrWhite
    Commented May 3, 2013 at 16:13
  • 2
    Thanks! Another useful one is: =sum(Filter(A2:A, B2:B > EOMonth(now(), -1))), which sums all of the values for the current month, where A2:A contains the values, and B2:B contains the associated date.
    – jpaugh
    Commented Jul 22, 2015 at 1:53
  • 1
    You're missing a closing parenthesis.
    – jpierson
    Commented Nov 10, 2018 at 18:44
  • comma or semicolon usage should be specified in the accepted answer
    – DependencyHell
    Commented Apr 2, 2019 at 10:57
  • while this answer and the one below perfectly answers the question, I wonder if there is a solution using QUERY, as it would be more "tweakable"
    – Théo Rubenach
    Commented Mar 16, 2021 at 16:50
35

There is also a builtin function in Google Spreadsheet for this. That is SUMIF

Using SUMIF the solution would be like this:

=SUMIF(B1:B6,"Yes",A1:A6)
Improve this answer
2
  • The same goes for this that goes for xylar's answer: the commas may need to be replaced with semicolons depending on language settings.
    – bmaupin
    Commented Mar 2, 2017 at 16:29
  • Most perfect solution
    – Silent Spectator
    Commented Dec 24, 2019 at 12:59

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