I'm sure I'm not the first person to have this question but I haven't been able to find anything online to help me solve my problem. I have a table of data and I need to pull the data from the last non-blank row in one of the columns in my table. Sounds simple enough but that's where you would be wrong. Normally I would be fine using =INDEX(Table1[Column X],COUNTA(Table1[Column X])) to find the last non-blank row of data. The problem with is that the column that I need to pull data from in the table that I'm working with isn't truly blank. Instead, there's a formula which is telling it to check for a certain condition, if that condition is met then spit out a value else the value in that cell is "". Even though the value in that cell may be "", since there is a formula which tells excel how to populate that cell, when using INDEX and COUNTA, excel treats the cell as if it's not blank and returns the wrong answer. What I need to figure out is how to find the last row which contains something other than "". For example, let's say I have the following table:
Date | Data 1 | Data 2 | Data 3 | Sum |
January 2020 | 1 | 2 | 3 | 6 |
February 2020 | 5 | 4 | 6 | 15 |
March 2020 | 2 | 8 | 3 | 13 |
April 2020 |
In the example table, let's say I need to pull data from the "Sum" column. In the example, the sum column would be populated with =IF(SUM(Data 1:Data 3)>0,SUM(Data 1:Data 3),""). If I were to use =INDEX(SUM,COUNTA(SUM)) then excel would return "" because April 2020 shows "" and is not truly blank because there is a formula inside the cell. What excel SHOULD be returning is March 2020's value of 13.