Google-sheets – G sheet How to jump over no value cells with SUMIF function

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

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 ?

Best Answer

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.

Formula

=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A")
=QUERY(DATA!A:B;"SELECT A, SUM(B) WHERE B IS NOT NULL GROUP BY A Label SUM(B)'Units'")

Explained

If you include headers, then the QUERY function will add them as such and alter them accordingly, ie. SUM title or COUNT title....etc. You can also added a custom label to the calculated columns, see 2^nd example (3^rd sheet). The rest of the function will perform as standard SQL.

Example

I've created an example file for you: sum over several cells

References

Google-sheets – Error using “sumif” together with “address” function

The problem is that address returns a string, so you need to turn that string into a reference.

Try this:

=sumif(indirect(address(F2,G2,2)&":"&address(ROW(B2),COLUMN(B2))),B2,indirect(address(F2,H2,2)&":"&address(ROW(C2),COLUMN(C2))))

Best Answer

Related Solutions

Google Sheets – How to Use an IF Statement Over Several Cells

Formula

Explained

Example

References

Google-sheets – Error using “sumif” together with “address” function

Related Topic