Suppose I have a table with 50 rows and 50 columns. Each row has a certain number of blank cells, often occurring in the last few columns, with the first few columns being relatively dense. I would like to find a way to count how many columns in a row, starting from the last column, are blank, ending the count with the first non-blank cell encountered. I would like to use this count to rank rows of the table. Is there an easy way to do this with formulas alone?
You can do this using an array formula. Say The 50 columns of data are in columns A through AX, row 2 is your first data row, and the formula is going into AZ2:
The formula needs to be entered using Ctrl-Shift-Enter so it goes in as an array formula. Once it is entered, you can copy and paste 49 more copies down column AZ. The result will be the number of consecutive blanks at the end of each row (50 minus the column of the last non-blank entry). I tested it and it works.