Find value of last non blank cell in a row (while skipping specific columns)

Question

So basically I have a list of data in a row. They are totaled weekly statistics numbers and at the end of every month, there is a "total" column. The column where the 8 is at is where this formula will go and the column with the 5 and 0 in it is where my data starts. The grey columns are the "total" columns.

I am trying to use this code =INDEX(J15:BT15,1,COUNT(J15:BT15)) to get the value of the last non-blank row. It doesn't work because the "total" column automatically populates as a sum of the weeks in that month for a "Month to date" kind of total. How would I go about skipping these "total" columns with the index formula, and if it isn't possible, how can I achieve this in a different way without relocating my data with formulas? Is there a way to do this with =query() by skipping columns with the header "total"? Very amateur at this so thank you in advance!

Answer 1

EXAMPLE 1:

=INDEX({A1,B1,D1},MAX(({A1,B1,D1}<>"")*COLUMN(A1:C1)))

• {A1,B1,D1} - included columns
• COLUMN(A1:C1) - full range (A1:D1) of columns (4 columns) minus excluded column (1) = A1:C1 (because 4 - 1 = 3)

---

EXAMPLE 2:

=INDEX({A1,B1,D1,E1,F1,I1},MAX(({A1,B1,D1,E1,F1,I1}<>"")*COLUMN(A1:F1)))

• excluded columns: C, G, H
• COLUMN(A1:F1) = range A1:I1 minus 3 excluded columns

---

EXAMPLE 3:

=INDEX({J1,K1,M1},MAX(({J1,K1,M1}<>"")*COLUMN(A1:C1)))