I have two columns. One is a value, the other is a date and time:

A.                B.   
01/01/21 5.15     3
01/01/21 5.15     2
01/01/21 5.15     4
01/01/21 5.30     2
01/01/21 5.30     3

I want the output to be:

A.                B.   
01/01/21 5.15     9
01/01/21 5.30     5

If your times in Col A are hour-minute as shown (i.e., no seconds):

=QUERY(A:B, "Select A, SUM(B) WHERE A Is Not Null GROUP BY A LABEL SUM(B) ''")

This assumes your values start in A1:B1 (i.e., that you have no headers).

A longer but more flexible and easily transferable version of the above:

=QUERY({A:B}, "Select Col1, SUM(Col2) WHERE Col1 Is Not Null GROUP BY Col1 LABEL SUM(Col2) ''")

  • Thanks, Erik - just tried that in my Sheet and it worked a treat! – Mark Harris Jun 12 at 17:41
  • 1
    Glad to hear it. QUERY is a useful and powerful tool, one worth exploring. – Erik Tyler Jun 13 at 4:19

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.