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?
Excel – How to count blank cells at end of row in n-column table
microsoft excelworksheet-function
Related Solutions
It's been a while so I'm hoping a LibreOffice solution may be better than nothing:
It's often the simplest method to extend the size of your table in advance for as many rows as you think you'll need. Sorting and formulas will continue to work as expected.
- Select your table
- In the Table Tools/Design menu, click Resize Table and specify a longer range.
Alternatively, the manual way is to select the last row in your table and do "Insert row below" (or use the little arrow in the bottom right cell). This could be automated using VBA if you're so inclined, although I usually find extending the table does the job just as well and makes it easier for the end-user to deal with.
Best Answer
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.