How can I count the rows with data in an Excel sheet

Question

I am trying to count the number of rows in a spreadsheet which contain at least one non-blank value over a few columns  i e   row 1 has a text value in column A row 2 has a text value in column B row 3 has a text value in column C row 4 has no values in A  B or C   The formula would equate to 3  because rows 1  2   amp  3 have a text value in at least one column  Similarly if row 1 had a text value in each column  A  B   amp  C  this would be counted as 1

User · Answer

Try this scenario    Array   A1 C7  A1-A3 have values  B2-B6 have value and C1  C3 and C6 have values    To get a count of the number of rows add a column D  you can hide it after formulas are set up  and in D1 put formula  If Sum A1 C1  gt 0 1 0   Copy the formula from D1 through D7  for others searching who are not excel literate  the numbers in the sum formula will change to the row you are on and this is fine     Now in C8 make a sum formula that adds up the D column and the answer should be 6  For visually pleasing purposes hide column D

User · Answer

You should use the sumif function in Excel    SUMIF A5 C10  Text to find  C5 C10   This function takes a range like this square A5 C10 then you have some text to find this text can be in A or B then it will add the number from the C-row

User · Answer

If you want a simple one liner that will do it all for you  assuming by no value you mean a blank cell      ROWS A A    ROWS B B    ROWS C C   - COUNTIF A C        If by no value you mean the cell contains a 0    ROWS A A    ROWS B B    ROWS C C   - COUNTIF A C  0    The formula works by first summing up all the rows that are in columns A  B  and C  if you need to count more rows  just increase the columns in the range  E g  ROWS A A    ROWS B B    ROWS C C    ROWS D D          ROWS Z Z     Then the formula counts the number of values in the same range that are blank  or 0 in the second example    Last  the formula subtracts the total number of cells with no value from the total number of rows  This leaves you with the number of cells in each row that contain a value

User · Answer

If you don t mind VBA  here is a function that will do it for you  Your call would be something like    CountRows 1 10     Function CountRows ByVal range As range  As Long  Application ScreenUpdating   False Dim row As range Dim count As Long  For Each row In range Rows     If  Application WorksheetFunction CountBlank row   - 256  lt  gt  0 Then         count   count   1     End If Next  CountRows   count Application ScreenUpdating   True  End Function   How it works  I am exploiting the fact that there is a 256 row limit  The worksheet formula CountBlank will tell you how many cells in a row are blank  If the row has no cells with values  then it will be 256  So I just minus 256 and if it s not 0 then I know there is a cell somewhere that has some value

User · Answer

This is what I finally came up with  which works great     SUM IF  ISTEXT  Worksheet Name A A    ISTEXT  CCSA Associates  E E   1 0  -1   Don t forget since it is an array to type the formula above without the       and to CTRL   SHIFT   ENTER instead of just ENTER for the      to appear and for it to be entered properly

User · Answer

With formulas  what you can do is      in a new column  say col D - cell D2   add  COUNTA A2 C2  drag this formula till the end of your data  say cell D4 in our example  add a last formula to sum it up  e g in cell D5    SUM D2 D4

[vba] How can I count the rows with data in an Excel sheet?

Examples related to vba

Examples related to excel

Examples related to excel-formula