44

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"))
  • 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 Tuinstra May 3 '13 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 Tuinstra May 3 '13 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 May 3 '13 at 16:11
52

Just realised FILTER should be comma separated:

=SUM(FILTER(A1:A5,B1:B5="Yes"))
| improve this answer | |
  • 6
    Note that whether you need to use a comma or semicolon to separate function arguments is dependent on your language settings. – MrWhite May 3 '13 at 16:13
  • 1
    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 Jul 22 '15 at 1:53
  • 1
    You're missing a closing parenthesis. – jpierson Nov 10 '18 at 18:44
  • comma or semicolon usage should be specified in the accepted answer – DependencyHell Apr 2 '19 at 10:57
16

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 | |
  • 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 Mar 2 '17 at 16:29
  • Most perfect solution – Silent Spectator Dec 24 '19 at 12:59

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