When I create a horizontal sum of cells, whose contents are generated using the SUMWHEN function (in german its SUMMEWENN), the sum is fine as long it does not exceed 24 h. When it exceeds 24 hours, the cell displays the difference to the next 24 h.
Formatting the summed cell with [hh]:mm does not help, the results gets even more weired.
Example: The final cell H7 has the formula =SUMME(B7:G7) and display a value of 11.30 h, which is incorrect, since it should be (see below) 59.30 h
All cells are formatted as Time 13:30 h. When I format cell H7 as user defined as [hh]:mm, the it displays the value 1739:30
B7 has the formula =SUMMEWENN(Mittwoch!B5:B17;Personal!A7;Mittwoch!E5:E17)
B7 displays a value of 0.00 h
C7 has the formula =SUMMEWENN(Donnerstag!$B$5:$B$21;Personal!A7;Donnerstag!$E$5:$E$21)
C7 displays a value of 10.00 h
D7 has the formula =SUMMEWENN(Freitag_Vorb!$B$5:$B$25;Personal!A7;Freitag_Vorb!$E$5:$E$25)+SUMMEWENN(Freitag_Fest!$B$5:$B$34;Personal!A7;Freitag_Fest!$E$5:$E$34)+SUMMEWENN(Freitag_Fest!$F$5:$F$34;Personal!A7;Freitag_Fest!$I$5:$I$34)
D7 displays a value of 16.30 h
E7 has the formula =SUMMEWENN(Samstag!$B$5:$B$35;Personal!A7;Samstag!$E$5:$E$35)+SUMMEWENN(Samstag!$F$5:$F$35;Personal!A7;Samstag!$I$5:$I$35)+SUMMEWENN(Samstag!$J$5:$J$35;Personal!A7;Samstag!$M$5:$M$35)
E7 dislays a value of 15.30 h
F7 has the formula =SUMMEWENN(Sonntag!$B$5:$B$41;Personal!A7;Sonntag!$E$5:$E$41)+SUMMEWENN(Sonntag!$F$5:$F$41;Personal!A7;Sonntag!$I$5:$I$41)+SUMMEWENN(Sonntag!$J$5:$J$41;Personal!A7;Sonntag!$M$5:$M$41)
F7 displays a value of 11.30 h
G7 has the formula =SUMMEWENN(Montag!$B$5:$B$21;Personal!A7;Montag!$E$5:$E$21)
G7 displays a value of 6.00 h
