Data in Cells

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.
 
S

SKY

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
 
S

Shreekant Patel

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
 
C

CarlosAntenna

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.
 
S

Shreekant Patel

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?
 
G

Gary

=(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
 
S

Shreekant Patel

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?
 
S

Shreekant Patel

Hello Gary,

All seems to be working fine.

Thanks for your help!

Shreekant
Have a good weekend!
 
Top