37

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
47

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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