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

" wrote:

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.

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


SKY wrote:

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

" wrote:

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.

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.

--
Carlos

"Shreekant Patel" wrote in message
oups.com...
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


SKY wrote:

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

" wrote:

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.

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?
CarlosAntenna wrote:

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.

--
Carlos

"Shreekant Patel" wrote in message
oups.com...
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


SKY wrote:

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

" wrote:

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.

=(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
wrote in message
ups.com...
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.

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?

srmp
Gary wrote:

=(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
wrote in message
ups.com...
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.

Hello Gary,

All seems to be working fine.

Thanks for your help!

Shreekant
Have a good weekend!
Shreekant Patel wrote:

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?

srmp
Gary wrote:

=(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
wrote in message
ups.com...
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.