Data in Cells

  • Thread starter Thread starter srmpatel
  • Start date Start date
S

srmpatel

Hello Everyone,

Hopefully the solution to this is quite straightforward.

I have an excel worksheet that has about 11000 cells. Some cells have
data in them and some do not.

I need if possible, to calculate the total % of how many cells have
data in them. Columns which are currently blank have been removed.

Could anyone please help me out on this.

Thank you

srmp.
 
Try this.

I will use 0,1 in another column to represent with or without data.
You will write a simple formula like,

=IF(A1=" ",0,1)

If A1 = space, set the cell to 0, else set the cell to 1.

Then you can calculate the total and percentage you need.

Regards,
SKY
 
Hi SKy,

Thanks for your quick reply. I am still abit confused with this. This
is some of the data in the worksheet.

ColumnA ColumnB ColC Col D
000037878800 STN161-54 STN161-54 Ellipse and ACA 0506
STN161-51
000037878500 STN161-51 STN161-51 Ellipse and ACA 0506
000037878800 STN161-54 STN161-54 Ellipse and ACA 0506

I tried implementing your formula at the end of column B and i just the
value 1. Could you please explain to me how to go about it again.

I need to know over all the columns the total number of cell which have
data.

thanks for ya help!
srmp
 
To get the total number of cells containing data in columns a thru d:
=COUNTA(A:D)
Be aware that if any of your cells have formulae that evaluate to null, they
will be counted even though they appear to be blank.
 
Ok maybe am doing something very wrong but every time i use that
function in the last cell in column b, i get the value 1. there are 3
rows filled should the count not be 3?
 
=(counta(a1:a10)/(countblank(a1:a10)+counta(a1:a10)))

now format the cell to percentage.

The range that i have given is just an example...u can change it
accordingly.

Let me know if it works.

GARY
 
Hi Gary,

That worked superbly! Thank you for that funtion. Have one question,
if i wanted to apply this function across my whole worksheet, i simply
just have to modify the column ranges in the formulae right?
 
Hello Gary,

All seems to be working fine.

Thanks for your help!

Shreekant
Have a good weekend!
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Excel Copy cells from one worksheet to another workseet - increment row & do again 0
Excel Average dates help 0
using the =page! feature to transpose a row to column 0
Excel How do I delete images in a specific range 1
Comparison of columns using Conditional Formatting 3
Formula for counting cells with value 'x' provided Col U = 'y' 5
Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS 1

Back
Top