Teethless mama wrote...
=LOOKUP(2,1/(D1:D65535<""),D:D)
+LOOKUP(2,1/(Sheet2!E1:E65535<""),Sheet2!E:E)
....

What happens when the last entry in a column is nonnumeric text or an
error value? Since the OP wants to sum the results, reasonably safe to
assume OP wants to find the last number in the column, in which case
shorter, more efficient and more robust to use

=LOOKUP(1E+307,D:D)+LOOKUP(1E+307,SheetX!E:E)