MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula help

Posted by Ron Doig on December 10, 2001 7:47 AM

I imagine this question is a simple one to answer but I cant find an answer on my own. I am summing a large column of information and need to divide the summation by the total number of entries in the column. This number of entries will increase as I add additional information. What is the syntax for a formula to count the number of entries (rows)in a column? Any help will be greatly appreciated.

Posted by Aladin Akyurek on December 10, 2001 8:03 AM

It seems you want to average a bunch of entries.


will average the numbers in A1:A100.

P.S. COUNT counts numeric entries, including dates.


Posted by aL on December 10, 2001 9:16 AM

You can use this as far down as needed, but the row numbers used must be only for those rows that have data. The formula can be as far down as you need it.

Posted by Juan Pablo G. on December 10, 2001 9:46 AM

To COUNT how many entries are in a column you can use:


for numeric entries only, or...


for all entries.

Posted by Horacio on December 10, 2001 10:07 AM

Hi Ron,
This is what I've used when I need to know how many rows with data I have in a column.
Type a formula on the same row (is better to do it next to the cell where your data is) like this =if(cell="","",1) what that means is that if there is data in the cell, it will return a one otherwise it will leave it blank. Then you can just add this row.
You can use the total of the row when you divide the total of your data.

Posted by Aladin Akyurek on December 10, 2001 10:09 AM

What about: =AVERAGE(A:A) ? [NT]

Posted by Mark W. on December 10, 2001 10:18 AM

That would be too easy! :) (nt)