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, 2019 at 1:39

1 Answer 1





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




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




  • 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, 2019 at 21:05
  • @TheMrIggs =INDEX({J1,K1,M1},MAX(({J1,K1,M1}<>"")*COLUMN(A1:C1)))
    – user0
    Jan 9, 2019 at 21:19
  • Works like a charm! How would I get the 2nd to last non blank using this formula?
    – TheMrIggs
    Jan 9, 2019 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, 2019 at 22:05
  • @TheMrIggs I see, try: =INDEX({J1,K1,M1},MAX(({J1,K1,M1}<>"")*COLUMN(A1:C1)-1))
    – user0
    Jan 9, 2019 at 22:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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