Hi

I have 20 cells in a single column in a sheet called "Audits"

All the cells have a formula in them.

The formula is a simple one that reads whatever I put into a cell on a
different worksheet called "Names"

ie: =Names!A1
= Names!A2

etc etc


I would like to have a formula that counts up all the results I get in the
sheet called "Audits"

At the moment if I enter (let say) 15 names in the "Names" sheet the result
is that I see the same 15 names in the "Audits" sheet, however the COUNTA
formula returns a total of 20. SO obviously it is counting the formulas in
the range.

How can I count these results without including the formulas that dont
return a value?


Thanks in advance

John

Your references returns a 0 until a name is entered? IF so...
=COUNTIF(A1:A646,"<0")


"John Calder" wrote:

Hi

I have 20 cells in a single column in a sheet called "Audits"

All the cells have a formula in them.

The formula is a simple one that reads whatever I put into a cell on a
different worksheet called "Names"

ie: =Names!A1
= Names!A2

etc etc


I would like to have a formula that counts up all the results I get in the
sheet called "Audits"

At the moment if I enter (let say) 15 names in the "Names" sheet the result
is that I see the same 15 names in the "Audits" sheet, however the COUNTA
formula returns a total of 20. SO obviously it is counting the formulas in
the range.

How can I count these results without including the formulas that dont
return a value?


Thanks in advance

John

Or you might try =COUNTIF(A1:A646,"0") if you still get the wrong number.

--
Best wishes,

Jim


"John Calder" wrote:

Hi

I have 20 cells in a single column in a sheet called "Audits"

All the cells have a formula in them.

The formula is a simple one that reads whatever I put into a cell on a
different worksheet called "Names"

ie: =Names!A1
= Names!A2

etc etc


I would like to have a formula that counts up all the results I get in the
sheet called "Audits"

At the moment if I enter (let say) 15 names in the "Names" sheet the result
is that I see the same 15 names in the "Audits" sheet, however the COUNTA
formula returns a total of 20. SO obviously it is counting the formulas in
the range.

How can I count these results without including the formulas that dont
return a value?


Thanks in advance

John

Thanks BoniM and Jim for your suggestion. However for some reason it does not
seem to work? Well, that not totally true, I entered some test data in the
same work sheet and it works fine, however on the range that I want it to
count it still adds up everything ?

The formula that is in each cell of the range that i want to add up is as
follows:

=IF(Names!A1="","",Names!A1)
=IF(Names!A2="","",Names!A2)
=IF(Names!A3="","",Names!A3)

etc etc (this goes for 20 rows)

I dont think it has anything to do with the way the above formula has been
created but I could be wrong. At present I have 17 names in the "Names" sheet
in column A but the COUNTIF formula still adds up 20?

Any Ideas ?


Thanks

John



"Jim Jackson" wrote:

Or you might try =COUNTIF(A1:A646,"0") if you still get the wrong number.

--
Best wishes,

Jim


"John Calder" wrote:

Hi

I have 20 cells in a single column in a sheet called "Audits"

All the cells have a formula in them.

The formula is a simple one that reads whatever I put into a cell on a
different worksheet called "Names"

ie: =Names!A1
= Names!A2

etc etc


I would like to have a formula that counts up all the results I get in the
sheet called "Audits"

At the moment if I enter (let say) 15 names in the "Names" sheet the result
is that I see the same 15 names in the "Audits" sheet, however the COUNTA
formula returns a total of 20. SO obviously it is counting the formulas in
the range.

How can I count these results without including the formulas that dont
return a value?


Thanks in advance

John

Try =COUNTIF(G1:G646,"""")

This one worked for me.
--
Best wishes,

Jim


"John Calder" wrote:

Thanks BoniM and Jim for your suggestion. However for some reason it does not
seem to work? Well, that not totally true, I entered some test data in the
same work sheet and it works fine, however on the range that I want it to
count it still adds up everything ?

The formula that is in each cell of the range that i want to add up is as
follows:

=IF(Names!A1="","",Names!A1)
=IF(Names!A2="","",Names!A2)
=IF(Names!A3="","",Names!A3)

etc etc (this goes for 20 rows)

I dont think it has anything to do with the way the above formula has been
created but I could be wrong. At present I have 17 names in the "Names" sheet
in column A but the COUNTIF formula still adds up 20?

Any Ideas ?


Thanks

John



"Jim Jackson" wrote:

Or you might try =COUNTIF(A1:A646,"0") if you still get the wrong number.

--
Best wishes,

Jim


"John Calder" wrote:

Hi

I have 20 cells in a single column in a sheet called "Audits"

All the cells have a formula in them.

The formula is a simple one that reads whatever I put into a cell on a
different worksheet called "Names"

ie: =Names!A1
= Names!A2

etc etc


I would like to have a formula that counts up all the results I get in the
sheet called "Audits"

At the moment if I enter (let say) 15 names in the "Names" sheet the result
is that I see the same 15 names in the "Audits" sheet, however the COUNTA
formula returns a total of 20. SO obviously it is counting the formulas in
the range.

How can I count these results without including the formulas that dont
return a value?


Thanks in advance

John

I just realized I forgot to return the cell references to your original. I
had some data in column "G" so used it as referenced cells. Sorry.
--
Best wishes,

Jim


"John Calder" wrote:

Thanks BoniM and Jim for your suggestion. However for some reason it does not
seem to work? Well, that not totally true, I entered some test data in the
same work sheet and it works fine, however on the range that I want it to
count it still adds up everything ?

The formula that is in each cell of the range that i want to add up is as
follows:

=IF(Names!A1="","",Names!A1)
=IF(Names!A2="","",Names!A2)
=IF(Names!A3="","",Names!A3)

etc etc (this goes for 20 rows)

I dont think it has anything to do with the way the above formula has been
created but I could be wrong. At present I have 17 names in the "Names" sheet
in column A but the COUNTIF formula still adds up 20?

Any Ideas ?


Thanks

John



"Jim Jackson" wrote:

Or you might try =COUNTIF(A1:A646,"0") if you still get the wrong number.

--
Best wishes,

Jim


"John Calder" wrote:

Hi

I have 20 cells in a single column in a sheet called "Audits"

All the cells have a formula in them.

The formula is a simple one that reads whatever I put into a cell on a
different worksheet called "Names"

ie: =Names!A1
= Names!A2

etc etc


I would like to have a formula that counts up all the results I get in the
sheet called "Audits"

At the moment if I enter (let say) 15 names in the "Names" sheet the result
is that I see the same 15 names in the "Audits" sheet, however the COUNTA
formula returns a total of 20. SO obviously it is counting the formulas in
the range.

How can I count these results without including the formulas that dont
return a value?


Thanks in advance

John

Well done Jim, works great !

Many Thanks

John


"Jim Jackson" wrote:

Try =COUNTIF(G1:G646,"""")

This one worked for me.
--
Best wishes,

Jim


"John Calder" wrote:

Thanks BoniM and Jim for your suggestion. However for some reason it does not
seem to work? Well, that not totally true, I entered some test data in the
same work sheet and it works fine, however on the range that I want it to
count it still adds up everything ?

The formula that is in each cell of the range that i want to add up is as
follows:

=IF(Names!A1="","",Names!A1)
=IF(Names!A2="","",Names!A2)
=IF(Names!A3="","",Names!A3)

etc etc (this goes for 20 rows)

I dont think it has anything to do with the way the above formula has been
created but I could be wrong. At present I have 17 names in the "Names" sheet
in column A but the COUNTIF formula still adds up 20?

Any Ideas ?


Thanks

John



"Jim Jackson" wrote:

Or you might try =COUNTIF(A1:A646,"0") if you still get the wrong number.

--
Best wishes,

Jim


"John Calder" wrote:

Hi

I have 20 cells in a single column in a sheet called "Audits"

All the cells have a formula in them.

The formula is a simple one that reads whatever I put into a cell on a
different worksheet called "Names"

ie: =Names!A1
= Names!A2

etc etc


I would like to have a formula that counts up all the results I get in the
sheet called "Audits"

At the moment if I enter (let say) 15 names in the "Names" sheet the result
is that I see the same 15 names in the "Audits" sheet, however the COUNTA
formula returns a total of 20. SO obviously it is counting the formulas in
the range.

How can I count these results without including the formulas that dont
return a value?


Thanks in advance

John