SUM problem

So in Tabel 1::D2 the formula is simply

=C2-B2

If End and Start times may sometimes be on different days then a more complicated formula is required.

In Tabel 2 we have to consider these possibilities:

• the sum is a plain value
• the sum is a duration
• the year doesn't exist in Tabel 1

So there are multiple conversion and an IFERROR to make sure we add the same type of values

In Tabel 2::B2

=IFERROR(DURATION(,,DUR2HOURS(SUMIF(Tabel 1::Year,A2,Tabel 1::Total))),DURATION(,,0))

The first and third arguments of SUMIF must be ranges, not single cells.

Double-click your formula to edit.

Click on the blue bubble in your formula, then on E above column E in Tabel 1.

Click on the pink bubble in your formula, then on D above column D in Tabel 1.

Type Enter to close editing.

The problem with SUM is that you try to sum plain numbers (0) with durations (3h), you have to set them all with the same type.