1

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.

enter image description here

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!

  • your question is highly ununderstandable from 2nd sentence to the end – user0 Jan 9 at 1:39
0

EXAMPLE 1:

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

5

  • {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)))

9

  • 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)))

4

  • So I tried using what your comment as a template and I can't get it to work. What am I doing wrong? The formula I typed wasn't for the full range I wanted it to, but I decreased the range to eliminate error but I had no luck. gyazo.com/8f052b93c39b072a8c5e890698da7215 – TheMrIggs Jan 9 at 21:05
  • @TheMrIggs =INDEX({J1,K1,M1},MAX(({J1,K1,M1}<>"")*COLUMN(A1:C1))) – user0 Jan 9 at 21:19
  • Works like a charm! How would I get the 2nd to last non blank using this formula? – TheMrIggs Jan 9 at 21:46
  • So the formula you provided allows me to skip the totaling columns AND get the last non blank cell in row, as shown in example 3. How would I get the second to last non blank cell in a row using that formula? So how would I get the value of 3 (in the cell K1) in Example 3? – TheMrIggs Jan 9 at 22:05
  • @TheMrIggs I see, try: =INDEX({J1,K1,M1},MAX(({J1,K1,M1}<>"")*COLUMN(A1:C1)-1)) – user0 Jan 9 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.