Counting cells that contain any data

[Dan Morris]

Hi there

I'd be really grateful if anyone could help me with this as I'm struggling to work out a formula to do this even though it sounds simple.

I want to count cells in a particular row or column that contain any data, ideally without having to specify a range, so I just want to know that in column C contains x or row 5 contains y amount of data. So it would look at the entire rown or column and work out how many cells contain something and shows how many.

If anyone knows, please tell me.

Thank you very much indeed.

Dan

 

[VoG]

Try like this

=COUNTA(C:C)

 

[Dan Morris]

Thank you VoG, but I don't seem to be able to make that work, I have a large sheet containing lots of (text) data and want to put a count in every column and every row telling me how many cells in that column or that row contain data...

 

[Rick Rothstein]

Thank you VoG, but I don't seem to be able to make that work, I have a large sheet containing lots of (text) data and want to put a count in every column and every row telling me how many cells in that column or that row contain data...

So you want to put a formula in each row and in each column to count the number of items in that row or column, right? Where is the formula to go... at the beginning of the row or column (like a header) or at the right or bottom of the row or column (like a total cell)? If the latter, then is there a fixed range that the data can be in? Is so, what is that range (we would need to know this so we can exclude the formula from the total for the row or column)?

 

[Dan Morris]

So you want to put a formula in each row and in each column to count the number of items in that row or column, right? Where is the formula to go... at the beginning of the row or column (like a header) or at the right or bottom of the row or column (like a total cell)? If the latter, then is there a fixed range that the data can be in? Is so, what is that range (we would need to know this so we can exclude the formula from the total for the row or column)?

Hi Rick; almost that yes. Ideally I would have in column C a count for all the data in that row from D onwards; and then in row 7 a count showing how many cells in that column from 8 downwards contain data.

Thank you for any help you can give me

 

[Rick Rothstein]

Hi Rick; almost that yes. Ideally I would have in column C a count for all the data in that row from D onwards; and then in row 7 a count showing how many cells in that column from 8 downwards contain data.

That information should have been in your first message. To make the following formulas more efficient, I am going to assume a maximum data column of Z and a maximum data row of 100 which I highlighted in red so you can see them easier (change these as needed, but try to keep the row ranges and column ranges as small as possible while still being able to accommodate your maximum amounts of possible future data). Okay, with that said, give these formulas a try...

Put this in C8 and copy down: =COUNTA(D8:Z8)

Put this in D7 and copy across: =COUNTA(D8:D100)

 

[Dan Morris]

Thank you so much RIck, that works a treat. I really appreciate your help.
Best wishes, Dan

 

[Rick Rothstein]

Thank you so much RIck, that works a treat. I really appreciate your help.
Best wishes, Dan

You are quite welcome, but you should know this is the same answer VoG would have given you 2-1/2 hours earlier had you given him the same information you gave me in Message #3. When asking a question in a forum, you must remember that as obvious as your data layout seems to you, and as obvious as what you want to do with it is to you, the people you are asking for help from have no idea what your data layout is, have no idea what you want to do with it and have no idea why you want to do it... so you have to tell us all this information up front, otherwise we will try to guess at it using "standard" layouts and assumptions from what you do tell us... and as you can see, we cannot always guess correctly.

 

[Dan Morris]

OK, understood; thanks very much for your much appreciated help and advice.