I need to calculate percent of cells from a different worksheet that contain
data. In doing this I only want to calculate cells with a State and State
Plan Name. All the states are currently listed but only a fraction of the
cells actually contain state plan names. Only the rows that contain "State
Plan Names" (in column C) have data in them, but all states are listed (in
column A). I have created an if statement on the one worksheet to seek out
only the States that have a State Plan Name (in cell B1 =if(c1="","",A1). The
problem is when I use the count function, it is counting the cells that
contain the blank cells with the if function.

For example
A | B | C | D
1 AK AK AK Medical Data } <- column B contains =if(c1="","",A1)
2 AL " " }
3 AR AR AR Medical No Data }
4 AZ " " }

Now, when I compute =counta(B1:B4) it is coming up as 4
I need to compute the percent of only the ones in "D" that contain a "State
Plan Name" overlooking the blanks because those rows are the only ones that
actually contain related data. All together I am trying to compute the
percentage of cells comlpete.

I would like for my function to be =counta(D1:D4)/counta(B1:B4) i should be
getting 75%

Also as I receive data from other states that do have a "State Plan Name", I
need them to be reflected by this count, so I can not simply do a paste
special.

My function currently looks like:

=IF(B43=INDIRECT("Medicaid_Medical!"&I43&"$2"),COU NTA(OFFSET(INDIRECT("Medicaid_Medical!"&I43&"$2"), 2,0,47,1))/COUNTA(States_Medical),0)

Can anyone think of a way to overlook the blank cells? I didn't think it
would be this difficult

one way
=counta(b1:b4)-countblank(b1:b4)


"IntricateFool" wrote:

I need to calculate percent of cells from a different worksheet that contain
data. In doing this I only want to calculate cells with a State and State
Plan Name. All the states are currently listed but only a fraction of the
cells actually contain state plan names. Only the rows that contain "State
Plan Names" (in column C) have data in them, but all states are listed (in
column A). I have created an if statement on the one worksheet to seek out
only the States that have a State Plan Name (in cell B1 =if(c1="","",A1). The
problem is when I use the count function, it is counting the cells that
contain the blank cells with the if function.

For example
A | B | C | D
1 AK AK AK Medical Data } <- column B contains =if(c1="","",A1)
2 AL " " }
3 AR AR AR Medical No Data }
4 AZ " " }

Now, when I compute =counta(B1:B4) it is coming up as 4
I need to compute the percent of only the ones in "D" that contain a "State
Plan Name" overlooking the blanks because those rows are the only ones that
actually contain related data. All together I am trying to compute the
percentage of cells comlpete.

I would like for my function to be =counta(D1:D4)/counta(B1:B4) i should be
getting 75%

Also as I receive data from other states that do have a "State Plan Name", I
need them to be reflected by this count, so I can not simply do a paste
special.

My function currently looks like:

=IF(B43=INDIRECT("Medicaid_Medical!"&I43&"$2"),COU NTA(OFFSET(INDIRECT("Medicaid_Medical!"&I43&"$2"), 2,0,47,1))/COUNTA(States_Medical),0)

Can anyone think of a way to overlook the blank cells? I didn't think it
would be this difficult

I just hit no on whether that was helpful or not, but it was...

Thank you....

"JMB" wrote:

one way
=counta(b1:b4)-countblank(b1:b4)


"IntricateFool" wrote:

I need to calculate percent of cells from a different worksheet that contain
data. In doing this I only want to calculate cells with a State and State
Plan Name. All the states are currently listed but only a fraction of the
cells actually contain state plan names. Only the rows that contain "State
Plan Names" (in column C) have data in them, but all states are listed (in
column A). I have created an if statement on the one worksheet to seek out
only the States that have a State Plan Name (in cell B1 =if(c1="","",A1). The
problem is when I use the count function, it is counting the cells that
contain the blank cells with the if function.

For example
A | B | C | D
1 AK AK AK Medical Data } <- column B contains =if(c1="","",A1)
2 AL " " }
3 AR AR AR Medical No Data }
4 AZ " " }

Now, when I compute =counta(B1:B4) it is coming up as 4
I need to compute the percent of only the ones in "D" that contain a "State
Plan Name" overlooking the blanks because those rows are the only ones that
actually contain related data. All together I am trying to compute the
percentage of cells comlpete.

I would like for my function to be =counta(D1:D4)/counta(B1:B4) i should be
getting 75%

Also as I receive data from other states that do have a "State Plan Name", I
need them to be reflected by this count, so I can not simply do a paste
special.

My function currently looks like:

=IF(B43=INDIRECT("Medicaid_Medical!"&I43&"$2"),COU NTA(OFFSET(INDIRECT("Medicaid_Medical!"&I43&"$2"), 2,0,47,1))/COUNTA(States_Medical),0)

Can anyone think of a way to overlook the blank cells? I didn't think it
would be this difficult

you're welcome.

"IntricateFool" wrote:

I just hit no on whether that was helpful or not, but it was...

Thank you....

"JMB" wrote:

one way
=counta(b1:b4)-countblank(b1:b4)


"IntricateFool" wrote:

I need to calculate percent of cells from a different worksheet that contain
data. In doing this I only want to calculate cells with a State and State
Plan Name. All the states are currently listed but only a fraction of the
cells actually contain state plan names. Only the rows that contain "State
Plan Names" (in column C) have data in them, but all states are listed (in
column A). I have created an if statement on the one worksheet to seek out
only the States that have a State Plan Name (in cell B1 =if(c1="","",A1). The
problem is when I use the count function, it is counting the cells that
contain the blank cells with the if function.

For example
A | B | C | D
1 AK AK AK Medical Data } <- column B contains =if(c1="","",A1)
2 AL " " }
3 AR AR AR Medical No Data }
4 AZ " " }

Now, when I compute =counta(B1:B4) it is coming up as 4
I need to compute the percent of only the ones in "D" that contain a "State
Plan Name" overlooking the blanks because those rows are the only ones that
actually contain related data. All together I am trying to compute the
percentage of cells comlpete.

I would like for my function to be =counta(D1:D4)/counta(B1:B4) i should be
getting 75%

Also as I receive data from other states that do have a "State Plan Name", I
need them to be reflected by this count, so I can not simply do a paste
special.

My function currently looks like:

=IF(B43=INDIRECT("Medicaid_Medical!"&I43&"$2"),COU NTA(OFFSET(INDIRECT("Medicaid_Medical!"&I43&"$2"), 2,0,47,1))/COUNTA(States_Medical),0)

Can anyone think of a way to overlook the blank cells? I didn't think it
would be this difficult