Looking to add a cell function that counts the number of blank cells in a column, but only to the bottom row of data. Each row in my spreadsheet is a customer order and the sheet keeps track of order assembly progress, each task is grouped into a column and if the cell is blank it means it is yet to be finished.
Tasks include columns F-K, other rows will not be counted
Goal: Number of incomplete(Blank) tasks in a column will be shown as a number at the top of the sheet.
Problem: When using =COUNTBLANK (x3:x) The function counts all blank rows to the bottom of the sheet, Need to limit this to the bottom of the data. That number changes depending on how many orders are on the sheet so this number needs to be adaptive to the bottom of the data(Last order)
Link to copy of Sheet: Order Priority Sheet
Any Additional Information, misunderstandings, or questions please ask. I understand if my descriptions were not clear and would be more than happy to help you help me!
Try:
=COUNTBLANK($A$3:INDEX($A:$A,LOOKUP(2,1/(LEN($A:$A)>0),ROW($A:$A))))
adjust the column references for your desired column
This part of the formula:
LOOKUP(2,1/(LEN($A:$A)>0),ROW($A:$A))
returns the last row number in column A that appears blank.
However, if, for example, you wanted to count all of the blanks in the range F3:Kn where n is the last used row in column A, then try this modification:
=COUNTBLANK($F$3:INDEX($F:$K,LOOKUP(2,1/(LEN($A:$A)>0),ROW($A:$A)),6))
If you just want to count each column separately, again, using column A to determine the last relevant row, then, (for column F):
=COUNTBLANK(F$3:INDEX(F:F,LOOKUP(2,1/(LEN($A:$A)>0),ROW($A:$A))))
and fill right