Count Number of Continuous Non-Blank cells which appear Last
Count the number of Continuous Non-Blank cells which occur last in the row (ie. the last group of non-blank cells)
Column A & B formulas Count the number of Continuous Non-Blank cells which occur last in the row (ie. the last group of non-blank cells).
Each row (viz. C2:AG2) has cells which are blank and/or have values – the formula in cell A2 / B2 Count the number of Continuous Non-Blank cells which occur last in the row (ie. the last group of non-blank cells).
A row may also have ALL blank cells (in which case the formula returns zero) or NO blank cells (in which case the formula returns the count of all cells).
Option 1 – Cell A2 formula: Enter formula in cell A2 and copy down – this is a non-array formula and uses LOOKUP.
Option 2 – Cell B2 formula: Enter formula as an Array formula (Ctrl+Shift+Enter) in cell B2 and copy down – uses FREQUENCY / LOOKUP and is shorter and more efficient than cell A2 formula.
———————————————————————————————–
Cell A2 formula the number of Continuous Non-Blank cells which occur last in the row C2:AG2 (ie. the last group of non-blank cells).
Cell A2 formula:
=IF(COUNTA(C2:AG2), LOOKUP(2, 1/(C2:AG2<>””), COLUMN(C2:AG2)- COLUMN(C2)+1)- IFERROR(LOOKUP(2, 1/(C2:INDEX( C2:AG2, LOOKUP(2, 1/( C2:AG2<>””), COLUMN(C2:AG2)- COLUMN(C2) +1))=””), COLUMN(C2:AG2)- COLUMN(C2)+1), 0),0)
Explanation – Cell A2 formula (Non-Array Formula):
1) COUNTA(C2:AG2): counts the number of non-blank cells in the range, and if zero the formula returns zero.
2) (C2:AG2<>””) : returns an array fo TRUE and FALSE values – {FALSE, FALSE, FALSE, TRUE, FALSE,TRUE, FALSE, FALSE, FALSE,FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE,…} – where TRUE represents cells which satisfy the condition of not being blank. 1/(B2:AF2<>””): returns an array of 1s and #DIV/0! Error values – {#DIV/0!,#DIV/0!, #DIV/0!,1, #DIV/0!,1, #DIV/0!,#DIV/0!, #DIV/0!,#DIV/0!, 1,#DIV/0!, #DIV/0!,#DIV/0!, 1,1,1,1, #DIV/0!,…} – dividing 1 by TRUE returns 1, and dividing 1 by FALSE returns a #DIV/0! error (TRUE converts to 1 and FALSE converts to zero when used in calculation). This is the lookup_vector argument of the LOOKUP function.
3) COLUMN(C2:AG2)- COLUMN(C2)+1: returns an array of numbers 1 to 31 representing the position of each cell – {1,2,3, 4,5,6, 7,8,…, 29,30, 31}. This is the result_vector argument of the LOOKUP function.
4) LOOKUP(2,1/(C2:AG2<>””), COLUMN(C2:AG2)- COLUMN(C2)+1): returns the position of last non-blank cell in the range C2:AG2 ie. 18. Using 2 as the looup_value in this manner, will return the LAST Match or Last occurrence of 1 in the lookup_vector, and thereby the value from the result_vector corresponding with this Last Match. If the LOOKUP function can’t find the lookup_value, the function matches the largest value in lookup_vector that is less than or equal to lookup_value. If the lookup_value is larger than the largest value in the lookup_vector, then the last value will be returned. If the lookup value is greater than the largest value in the lookup_vector, then the search starts from the bottom or right, and in this search blanks and errors are ignored. Hence the number 2 can be replaced by any number which is larger than 1.
5) C2:INDEX(C2:AG2,LOOKUP(2, 1/(C2:AG2<>””), COLUMN(C2:AG2)- COLUMN(C2)+1)): returns the range values starting from the 1st cell of C2 till the position of the last non-blank cell in the range as determined in 4) above (ie. 18) – {0,0, 0,1, 0,1, 0,0, 0,0, 1,0, 0,0,1, 1, 1,1}.
6) LOOKUP(2,1/(C2:INDEX( C2:AG2, LOOKUP(2, 1/(C2:AG2<>””), COLUMN(C2:AG2)- COLUMN(C2)+1))=””), COLUMN(C2:AG2)- COLUMN(C2)+1): this does a similar lookup on the above range of 5) above to determine the last blank cell and returns the position (ie. 14) of last blank cell preceding the last group of continuous non-blank cells. IFERROR is used to return zero in case there is no blank cell preceding the last group of non-blank cells ie. zero is returned in the case where the range C2:AG2 has all non-blank cells.
7) Deducting 6) above from 4) above returns the count of the last group of non-blank cells in the range C2:AG2. Position of last blank cell preceding the last continuous non-blank cells is deducted from the position of last non-blank cell to return the number of continuous non-blank cells which appear last.
———————————————————————————————–
Related Formulas – illustrated in the Ebook of Excel Formulas.
Cell B2 Formula: Enter formula as an Array formula (Ctrl+Shift+Enter) in cell B2 and copy down – uses FREQUENCY / LOOKUP and is much shorter and more efficient than cell A2 formula. The LOOKUP formula is used in cell A2 to return the position of the last non-blank cell in the row, and this LOOKUP is repeated to determine the range from the 1st cell till this last non-blank cell. But the cell B2 formula does not repeat this calculation and is shorter than the cell A2 formula.