COUNTIF
 

I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences of a
statement, however I want to exclude from the count if a column contains an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I want
to count) and also alphanumeric (which I want to exclude). How do I make up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

It seems like the COUNT function would do what you want. It only counts
numeric cells.

Example:
=COUNT('myworksheet'!$A2:$A500)
that will only count the numeric cells in $A2:$A500 on myworksheet

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"cestbarb" wrote:

I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences of a
statement, however I want to exclude from the count if a column contains an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I want
to count) and also alphanumeric (which I want to exclude). How do I make up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

Hi

Try this:
=SUMPRODUCT(--(ISNUMBER(A2:A500)))

Andy.

"cestbarb" wrote in message
...
I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences of
a
statement, however I want to exclude from the count if a column contains
an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I want
to count) and also alphanumeric (which I want to exclude). How do I make
up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

I missed your worksheet ref:
=SUMPRODUCT(--(ISNUMBER(myworksheet!A2:A500)))


<Andy wrote in message ...
Hi

Try this:
=SUMPRODUCT(--(ISNUMBER(A2:A500)))

Andy.

"cestbarb" wrote in message
...
I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences
of a
statement, however I want to exclude from the count if a column contains
an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I
want
to count) and also alphanumeric (which I want to exclude). How do I make
up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here
I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

Thanks, but I did leave a small part out - what I'm trying to do is count log
assignments. In "A" I have a log number (which can be alphanumeric) in
column "J" I have a name (of the person to whom it was assigned) I need to
count the number of logs that were assigned to each of 10 people so I guess I
have to use COUNTIF.
Barb

"Ron Coderre" wrote:

It seems like the COUNT function would do what you want. It only counts
numeric cells.

Example:
=COUNT('myworksheet'!$A2:$A500)
that will only count the numeric cells in $A2:$A500 on myworksheet

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"cestbarb" wrote:

I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences of a
statement, however I want to exclude from the count if a column contains an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I want
to count) and also alphanumeric (which I want to exclude). How do I make up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

Just for the record....that wasn't exactly a "small part" that was left out :\

Perhaps something like this would work:

For an employee name in A1
B1:=SUMPRODUCT(('myworksheet'!$J$2:$J$500=A1)*('my worksheet'!$A$2:$A$500<""))

OR an easier option might be to set up a Pivot Table. It could
automatically create a table listing each employee on myworksheet and their
corresponding count of log assignments.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"cestbarb" wrote:

Thanks, but I did leave a small part out - what I'm trying to do is count log
assignments. In "A" I have a log number (which can be alphanumeric) in
column "J" I have a name (of the person to whom it was assigned) I need to
count the number of logs that were assigned to each of 10 people so I guess I
have to use COUNTIF.
Barb

"Ron Coderre" wrote:

It seems like the COUNT function would do what you want. It only counts
numeric cells.

Example:
=COUNT('myworksheet'!$A2:$A500)
that will only count the numeric cells in $A2:$A500 on myworksheet

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"cestbarb" wrote:

I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences of a
statement, however I want to exclude from the count if a column contains an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I want
to count) and also alphanumeric (which I want to exclude). How do I make up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

SO confusing, Thanks Andy, but the formula brought out a dialogue box Update
values "my worksheet" Is that what should happen?
Also did you see the second part of my query where I have to segregate by
name as well? Sorry for being so dumb.
Barb

"Andy" wrote:

I missed your worksheet ref:
=SUMPRODUCT(--(ISNUMBER(myworksheet!A2:A500)))


<Andy wrote in message ...
Hi

Try this:
=SUMPRODUCT(--(ISNUMBER(A2:A500)))

Andy.

"cestbarb" wrote in message
...
I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences
of a
statement, however I want to exclude from the count if a column contains
an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I
want
to count) and also alphanumeric (which I want to exclude). How do I make
up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here
I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

Oh gosh! - one more thing
Does it matter if the log numbers are separated by a dash as in 06-0033?
I'm guessing it makes a difference.
Barb

"Andy" wrote:

I missed your worksheet ref:
=SUMPRODUCT(--(ISNUMBER(myworksheet!A2:A500)))


<Andy wrote in message ...
Hi

Try this:
=SUMPRODUCT(--(ISNUMBER(A2:A500)))

Andy.

"cestbarb" wrote in message
...
I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences
of a
statement, however I want to exclude from the count if a column contains
an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I
want
to count) and also alphanumeric (which I want to exclude). How do I make
up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here
I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

Thank you Andy and Ron.
I'll try and see if it works.

"cestbarb" wrote:

SO confusing, Thanks Andy, but the formula brought out a dialogue box Update
values "my worksheet" Is that what should happen?
Also did you see the second part of my query where I have to segregate by
name as well? Sorry for being so dumb.
Barb

"Andy" wrote:

I missed your worksheet ref:
=SUMPRODUCT(--(ISNUMBER(myworksheet!A2:A500)))


<Andy wrote in message ...
Hi

Try this:
=SUMPRODUCT(--(ISNUMBER(A2:A500)))

Andy.

"cestbarb" wrote in message
...
I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences
of a
statement, however I want to exclude from the count if a column contains
an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I
want
to count) and also alphanumeric (which I want to exclude). How do I make
up
the formula. To further complicate matters I'm doing the calculations on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 -- here
I
want to say count if it contains only a number --
Is this clear?
Thanks.

COUNTIF
 

Hi

When SUMPRODUCT returns results, it defaults to TRUEs and FALSEs. The double
minus coerces the results to be 1s and 0s. A good explanation can be read
he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Read the FORMAT OF SUMPRODUCT section about halfway through.

Andy.

"Nuraq" wrote in message
...
Andy,

What is the purpose of the "--" before the ISNUMBER?

<Andy wrote in message ...
I missed your worksheet ref:
=SUMPRODUCT(--(ISNUMBER(myworksheet!A2:A500)))


<Andy wrote in message ...
Hi

Try this:
=SUMPRODUCT(--(ISNUMBER(A2:A500)))

Andy.

"cestbarb" wrote in message
...
I'll try to make this simple -
I have a large spreadsheet and I need to calculate how many occurrences
of a
statement, however I want to exclude from the count if a column
contains an
alpha character.
Example - a spreadsheet contains numbers in the first column (which I
want
to count) and also alphanumeric (which I want to exclude). How do I
make up
the formula. To further complicate matters I'm doing the calculations
on
another worksheet. So far I have COUNTIF'myworksheet'!$A2-$A500 --
here I
want to say count if it contains only a number --
Is this clear?
Thanks.