counting data in columns with linked cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bernie R. - 04 Feb 2008 15:50 GMT

I have a spreadsheet with numerous columns of data containing dates linked
from other spreadsheets. I'm using the "counta" to count the rows containing
data in each column, but my problem is every row is being counted whether it
contains data or is blank.

Appreciate anyone's assistance in solving this.

Reply to this Message

Roger Govier - 04 Feb 2008 16:03 GMT

Hi Bernie

Because you have a formula in the cell which pulls data from another sheet,
Counta is counting this.
As the results of the formula, when true, will be dates which will be serial
numbers, try
=COUNTIF(A:A,">0")

Signature

Regards
Roger Govier

> I have a spreadsheet with numerous columns of data containing dates linked
> from other spreadsheets. I'm using the "counta" to count the rows
[quoted text clipped - 4 lines]
>
> Appreciate anyone's assistance in solving this.

Reply to this Message

Bernie R - 04 Feb 2008 19:15 GMT

That worked; however, I got a curve ball thrown at me. If there are "NA" in
the cell(s), I don't want to count them or the blanks; and then take the "NA"
out of the demoninator when caluculating percentages for that column.

> Hi Bernie
>
[quoted text clipped - 12 lines]
> >
> > Appreciate anyone's assistance in solving this.

Reply to this Message

Roger Govier - 04 Feb 2008 22:48 GMT

Then try
=COUNTIF(A:A,">0")-COUNTIF(A:A,NA())

Signature

Regards
Roger Govier

> That worked; however, I got a curve ball thrown at me. If there are "NA"
> in
[quoted text clipped - 22 lines]
>> >
>> > Appreciate anyone's assistance in solving this.

Reply to this Message

Peo Sjoblom - 04 Feb 2008 16:05 GMT

Are the dates numbers so that when linked to an empty cell it will return a
zero
if so you can use

=COUNTIF(A1:A20,">0")

if text change the link formula to (adapt to fit you own references)

=IF(A2="","",A2)

as an example, if A2 is blank it will return a blank and not zero

=SUMPRODUCT(--(A1:A20<>""))

Signature

Regards,

Peo Sjoblom

>I have a spreadsheet with numerous columns of data containing dates linked
> from other spreadsheets. I'm using the "counta" to count the rows
[quoted text clipped - 4 lines]
>
> Appreciate anyone's assistance in solving this.

Reply to this Message