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!
Best Answer
EXAMPLE 1:
=INDEX({A1,B1,D1},MAX(({A1,B1,D1}<>"")*COLUMN(A1:C1)))
{A1,B1,D1}
- included columnsCOLUMN(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)))
C, G, H
COLUMN(A1:F1)
= rangeA1:I1
minus 3 excluded columnsEXAMPLE 3:
=INDEX({J1,K1,M1},MAX(({J1,K1,M1}<>"")*COLUMN(A1:C1)))