Possible sumproduct to find/sum the last entry in each row
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 |
Possible sumproduct to find/sum the last entry in each row
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(P5:P465) if there is nothing else in column P, or: =SUM(P5,P25,P45 ... P465) if there is. Hope this helps. Pete On Apr 21, 5:02*pm, Sarah (OGI) wrote: 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 |
Possible sumproduct to find/sum the last entry in each row
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. "Pete_UK" wrote: 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(P5:P465) if there is nothing else in column P, or: =SUM(P5,P25,P45 ... P465) if there is. Hope this helps. Pete On Apr 21, 5:02 pm, Sarah (OGI) wrote: 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 |
All times are GMT +1. The time now is 11:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com