G sheet How to jump over no value cells with SUMIF function

Question

I would to get the sum of the duration of the column B

+---+----------+
| A | 14:45:00 |
+---+----------+
| A | #Value!  |
+---+----------+
| A | 13:34:50 |
+---+----------+
| B | 23:41:00 |
+---+----------+
| B | 43:46:00 |
+---+----------+

I'm using these functions :

=SUMIF( A1:A5 ; "B" ;B1:B5 ) this one gives me the sum of time

=SUMIF( A1:A5 ; "A" ;B1:B5 ) This one doesn't work because I've an Error with #Value! in B2.

How to jump over the "#Value! Error" cells with SUMIF ?

Answer 1

There are two criteria:

• Must have A in column A

• Column B must have a value.

So, use SUMIFS

Formula:

=SUMIFS(B1:B5,A1:A5,"A",B1:B5,">0")

Here is the screenshot of where I used the formula:

Also, make sure format is set to duration where you are performing the sum.

Answer 2

Please use the following formula instead:

=SUM(QUERY(A1:B5,"where A='A'"))

Functions used:

• QUERY
• SUM