Count Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Calder - 19 Apr 2007 04:16 GMT

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

Reply to this Message

BoniM - 19 Apr 2007 05:20 GMT

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

> Hi
>
[quoted text clipped - 24 lines]
>
> John

Reply to this Message

Jim Jackson - 19 Apr 2007 15:42 GMT

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

Signature

Best wishes,

Jim

> Hi
>
[quoted text clipped - 24 lines]
>
> John

Reply to this Message

John Calder - 23 Apr 2007 00:20 GMT

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

> Or you might try =COUNTIF(A1:A646,">0") if you still get the wrong number.
>
[quoted text clipped - 26 lines]
> >
> > John

Reply to this Message

Jim Jackson - 23 Apr 2007 13:52 GMT

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

This one worked for me.

Signature

Best wishes,

Jim

> 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
[quoted text clipped - 50 lines]
> > >
> > > John

Reply to this Message

John Calder - 24 Apr 2007 02:10 GMT

Well done Jim, works great !

Many Thanks

John

> Try =COUNTIF(G1:G646,">""")
>
[quoted text clipped - 54 lines]
> > > >
> > > > John

Reply to this Message

Jim Jackson - 23 Apr 2007 13:54 GMT

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.

Signature

Best wishes,

Jim

Reply to this Message