Possible sumproduct to find/sum the last entry in each row

[Sarah (OGI)]

I have 24 rows of 12 cells, starting at row 5 and then every 20th row after
that (i.e. D5:O5, then D25:O25, then D45:O45....up to D465:O465).

I need to find the last entry (a cell that does not display a zero) in each
of the 24 rows (which may differ from one row to the next) and then do a sum
to add each value together.

For example, if the last entry in D5:O5 was £10 in F5, and the last entry in
D25:O25 was £12 in G25, then the result would be £22.

What would be the best way to go about this?

Many thanks in advance

 

[Pete_UK]

I'd put this formula in P5:

=LOOKUP(10^10,D5:O5)

and then copy it to P25, P45 etc. Then you can just do:

=SUM(P5465)

if there is nothing else in column P, or:

=SUM(P5,P25,P45 ... P465)

if there is.

Hope this helps.

Pete

 

[Sarah (OGI)]

Many thanks for your response, Pete.

It works fine when the cell range(s) contain numeric values and blanks.
However (as I should have mentioned before, my apologies), each of the cells
in the specified ranges, i.e. D5:O5,D25:O25, etc contain formulas, cross
referencing data on another worksheet through a vlookup.

Is there any way around this? Apologies again.