41

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
48

Just realised FILTER should be comma separated:

=SUM(FILTER(A1:A5,B1:B5="Yes"))
  • 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 at 10:57
14

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)
  • 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

protected by Community Oct 23 at 22:50

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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