Count Problem

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

This one worked for me.

 

[Guest]

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.

 

[Guest]

Well done Jim, works great !

Many Thanks

John

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

This one worked for me.