How to extract data from last columns in Google Sheets with scattered empty cells?

Other Questions Google Sheets
1

I’m working on a Google Sheets project and need some help. My main table spans rows 1 to 7 and grows by one column every week. The challenge is the presence of empty cells scattered throughout the table.

I need to:

  1. Extract the values from the last column (which updates periodically).
  2. Compute the sum of the last three columns (which also update periodically).

I have tried using index and counta functions, but they seem to work only when all cells contain values. Any suggestions on how to process this data with blank cells would be really helpful.

Below is a sample layout of what I’m trying to achieve:

| A | B | C | D | E |
|---|---|---|---|---|
| 1 | 2 |   | 4 | 5 |
| 6 |   | 8 | 9 |   |
|---|---|---|---|---|
| Last col:  | 5 |   |   |
| Sum last 3:  | 9 |   |   |

Thanks in advance for your advice!

2

For extracting the last column values, you might want to explore the LOOKUP function. Try this formula: =LOOKUP(2,1/(A1:E7<>“”),A1:E7). It’ll return the last non-empty value in each row.

As for summing the last three columns, consider using OFFSET combined with SUM. Something like: =SUM(OFFSET(A1,0,COUNTA(A1:1)-3,7,3)).

These formulas should handle scattered empty cells without issues. They’re quite robust and will adjust automatically as your sheet grows. Let me know if you need any clarification on how these work.

3

I’ve dealt with similar issues in my spreadsheets before. One approach that’s worked well for me is using array formulas. For the last column, try this:

=ARRAYFORMULA(IF(ROW(A1:A7)=ROW(A1:A7),INDEX(A1:E7,0,MAX((A1:E7<>“”)*COLUMN(A1:E7))),“”))

This should grab the last non-empty value in each row, regardless of scattered blanks.

For summing the last three columns, you could use:

=ARRAYFORMULA(SUM(OFFSET(A1:E7,0,COLUMNS(A1:E7)-3,ROWS(A1:E7),3)))

This dynamically adjusts as you add columns. It’s been pretty reliable in my experience, even with inconsistent data. Hope this helps solve your problem!

4

hey mike, have you tried the FILTER function? it could help with those pesky empty cells. something like =FILTER(A1:E7, A1:E7<>“”) might do the trick. then you can use INDEX to grab the last column. for the sum, maybe SUMIF could work? just brainstorming here, hope it helps!