Count rows with not empty value

Question

In a Google Spreadsheet  How can I count the rows of a given area that have a value  All hints about this I found up to now lead to formulas that do count the rows which have a not empty content  including formula   but a cell with     IF 1 2            Shows an empty cell   is counted as well   What is the solution to this simple task

User · Accepted Answer

Make another column that determines if the referenced cell is blank using the function  CountBlank    Then use count on the values created in the new  CountBlank  column

User · Answer

Solved using a solution i found googling by Yogi Anand  https   productforums google com d msg docs 3qsR2m-1Xx8 sSU6Z6NYLOcJ  The example below counts the number of non-empty rows in the range A3 C  remember to update both ranges in the formula with your range of interest    ArrayFormula SUM SIGN MMULT LEN A3 C   TRANSPOSE SIGN COLUMN A3 C          Also make sure to avoid circular dependencies  it will happen if you for example count the number of non-empty rows in A C and place this formula in the A or C column

User · Answer

You can define a custom function using Apps Script  Tools   Script editor  called for example numNonEmptyRows    function numNonEmptyRows range      Logger log  inside      Logger log range     if  range  amp  amp  range constructor     Array        return range map function a  return a join       filter Boolean  length       else       return range   1   0          And then use it in a cell like this  numNonEmptyRows A23 C25  to count the number of non empty rows in the range A23 C25

User · Answer

counta range      counta   Returns a count of the number of values in a dataset   Note  CountA considers    to be a value  Only cells that are blank  press delete in a cell to blank it  are not counted     Google support  https   support google com docs answer 3093991 countblank   Returns the number of empty cells in a given range   Note  CountBlank considers both blank cells  press delete to blank a cell  and cells that have a formula that returns    to be empty cells   Google Support  https   support google com docs answer 3093403   If you have a range that includes formulae that result in     then you can modify your formula from   counta range    to    Counta range  - Countblank range    EDIT  the function is countblank  not countblanks  the latter will give an error

User · Answer

A very flexible way to do that kind of things is using ARRAYFORMULA   As an example imagine you want to count non empty strings  text fields  you can use this code    ARRAYFORMULA SUM IF Len B3 B14  gt 0  1  0      What happens here is that  ArrayFormula  let you operate over a set of values  Using the SUM function you indicates  ArrayFormula  to sum any value of the set  The  If  clause is only used to check  empty  or  not empty   1 for not empty and 0 otherwise   Len  returns the length of the different text fields  there is where you define the set  range  you want to check  Finally  ArrayFormula  will sum 1 for each field inside the set range  in which  len  returns more than 0   If you want to check any other condition  just modify the first argument of the IF clause

User · Answer

Here s what I believe is the best solution so far    CountIf ArrayFormula range lt  gt     TRUE    Here s why in 3 easy steps  Step 1  Simple As Pie - Add Extra Column  The answer by eniacAvenger will yield the correct solution without worrying about edge cases as  A1 lt  gt    seems to arrive at the correct truthy falsy value based on how we intuitively think of blank cells  either virgin blanks or created blanks     So imagine we have this data and we want the Count of non-blanks in B2 B6              A          B        C       --- ------------- ------- ---------    1   Description   Value   B1 lt  gt         2   Text          H       TRUE        3   Number        1       TRUE        4   IF -                  FALSE       5   IF -  Text    h       TRUE        6   Blank                 FALSE       If we relied on Column C  we could get the count of values in B like this    COUNTIF C2 C6 True    Step 2  Use FormulaArray to dynamically create Extra Column  However  consideRatio s comment is a valid one - if you need an extra column  you can often accomplish the same goal with an ArrayFormula which can create a column in memory without eating up sheet space   So if we want to create C dynamically  we can use an array formula like this    ArrayFormula B2 B6 lt  gt       If we simply put it in C2  it would create the vertical array with a single stroke of the pen              A          B        C                        --- ------------- ------- --------------------------    1   Description   Value    ArrayFormula B2 B6 lt  gt         2   Text          H       TRUE                         3   Number        1       TRUE                         4   IF -                  FALSE                        5   IF -  Text    h       TRUE                         6   Blank                 FALSE                        Step 3  Count Values in Dynamic Column  But with that solved  we no longer need the column to merely display the values   ArrayFormula will resolve to the following range   True True False True False   CountIf just takes in any range and in this case can count the number of True values   So we can wrap CountIf around the values produced by ArrayFormula like this    CountIf ArrayFormula B2 B6 lt  gt     TRUE    Further Reading  The other solutions in this thread are either overly complex  or fail in particular edge cases that I ve enumerated in this test sheet   Google Spreadsheet - CountA Test - Demo  For why CountA works the wonky way it does  see my answer here

User · Answer

As far as I can see  most of the solutions here count the number of non empty cells  and not the number of rows with non empty cell inside   One possible solution for the range B3 E29 is for example    SUM ArrayFormula IF B3 B29 amp C3 C29 amp D3 D29 amp E3 E29    0 1      Here ArrayFormula IF B3 B29 amp C3 C29 amp D3 D29 amp E3 E29    0 1   returns a column of 0  if the row is empty  and 1  else     Another one is given in consideRatio s answer

User · Answer

In Google Sheets  to count the number of rows which contain at least one non-empty cell within a two-dimensional range    ARRAYFORMULA    SUM      N        MMULT          N A1 C5 lt  gt              TRANSPOSE COLUMN A1 C5  0                 gt 0               Where A1 C5 is the range you re checking for non-empty rows   The formula comes from  and is explained in the following article from EXCELXOR - https   excelxor com 2015 03 30 counting-rows-where-at-least-one-condition-is-met

User · Answer

It works for me   SUMPRODUCT NOT ISBLANK F2 F     Count of all non-empty cells from F2 to the end of the column

User · Answer

A simpler solution that works for me    COUNTIFS A A   lt  gt   amp       It counts both numbers  strings  dates  etc that are not empty

User · Answer

Given the range A A  Id suggest    COUNTA A A - COUNTIF A A     -COUNTIF A A          The problem is COUNTA over-counts by exactly the number of cells with zero length strings      The solution is to find a count of exactly these cells  This can be found by looking for all text cells and subtracting all text cells with at least one character   COUNTA A A   cells with value  including    but excluding truly empty cells COUNTIF A A       cells recognized as text  including    but excluding truly blank cells COUNTIF A A        cells recognized as text with at least one character   This means that the value COUNTIF A A     -COUNTIF A A       should be the number of text cells minus the number of text cells that have at least one character i e  the count of cells containing exactly

User · Answer

For me  none of the answers worked for ranges that include both virgin cells and cells that are empty based on a formula  e g   IF 1 2          What solved it for me is this    COUNTA FILTER range  range  lt  gt

User · Answer

I just used  COUNTIF Range    lt  gt    and it counted non-empty cells for me

[google-sheets] Count rows with not empty value

Examples related to google-sheets

Examples related to google-sheets-formula