Forum Discussion

kittenmeants's avatar
kittenmeants
Brass Contributor
Jul 14, 2023
Solved

Formula: Another way to count blank/empty cells

I need support in counting the number of empty cells (blanks cells), that do not have data entered, but omitting the title headers. On a (very) small scale this is what our tracking looks like. We ne...
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jul 14, 2023

    kittenmeants 

     

    Are the teams such as 2.1 and 3.8 all numbers? If so:

     

    With Team headers in column A and Met Goal in column 😧

     

    =SUM(ISNUMBER(A2:A1000)*(D2:D1000=""))

LeonPavesic's avatar
LeonPavesic
Silver Contributor
Jul 14, 2023

Hello kittenmeants,

To count the number of empty cells excluding the title headers in your tracking sheet, you can try a combination of the COUNTBLANK and OFFSET functions. You can try with this formula to dynamically count the empty cells, even when new teams are added to the sheet. Here's how you could implement it:

1. Use the following formula:


=COUNTBLANK(OFFSET(A2, 1, 0, COUNTA(A:A) - 1, 1))

- The OFFSET function is used to define the range of cells to count. It starts from cell A2 (excluding the title header) and extends for the number of rows determined by the COUNTA function.

- COUNTA(A:A) counts the number of non-empty cells in column A, and subtracting 1 ensures that the title header is excluded from the count.

- COUNTBLANK function calculates the number of empty cells within the specified range.

 

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.


If the post was useful in other ways, please consider giving it Like.


Kindest regards,


Leon Pavesic

Resources