google-sheetsgoogle-sheets-formulacellnonblank

Google Sheets: Cell Formula That Picks Up Last Non-Blank Cell Values in Columns PRIOR TO Another Cell

I have a cell (H10) that shows the result of a cell formula:

=sum(D43:D53)/sum(E43:E53)

I want the formula above to always start at row 43 (as shown) but the issue is the last row. The last row changes so I don't want it hard coded as D53 and E53.

Instead, I want the "last cells" in my formula to be the last cells with a non-blank value that is above cell D100 and E100. For example my last populated cell could be D54 and E54 or D94 or E94, etc. I want the formula to correctly pick up the last cells.

The reason is because I have another table in later rows and I don't want to pick up those later cell values.

Solution

  • You could just use it until row 99, if there are empty cells, they'll be dismissed:

    =sum(D43:D99)/sum(E43:E99)