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

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)

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

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 LongApplication.ScreenUpdating = FalseDim row As rangeDim count As LongFor Each row In range.Rows    If (Application.WorksheetFunction.CountBlank(row)) - 256 <> 0 Then        count = count + 1    End IfNextCountRows = countApplication.ScreenUpdating = TrueEnd 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.