COUNTIF:many occurrences

[cestbarb]

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.

[Ron Coderre]

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.

Hi

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

Andy.

"cestbarb" <[email protected]> wrote in message
news:[email protected]...
> 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.

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


<Andy> wrote in message news:%[email protected]...
> Hi
>
> Try this:
> =SUMPRODUCT(--(ISNUMBER(A2:A500)))
>
> Andy.
>
> "cestbarb" <[email protected]> wrote in message
> news:[email protected]...
>> 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.
>
>

[cestbarb]

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.

[Ron Coderre]

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)*('myworksheet'!$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.

[cestbarb]

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 news:%[email protected]...
> > Hi
> >
> > Try this:
> > =SUMPRODUCT(--(ISNUMBER(A2:A500)))
> >
> > Andy.
> >
> > "cestbarb" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >
> >
>
>
>

[cestbarb]

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 news:%[email protected]...
> > Hi
> >
> > Try this:
> > =SUMPRODUCT(--(ISNUMBER(A2:A500)))
> >
> > Andy.
> >
> > "cestbarb" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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.
> >
> >
>
>
>

[cestbarb]

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 news:%[email protected]...
> > > Hi
> > >
> > > Try this:
> > > =SUMPRODUCT(--(ISNUMBER(A2:A500)))
> > >
> > > Andy.
> > >
> > > "cestbarb" <[email protected]> wrote in message
> > > news:[email protected]...
> > >> 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.
> > >
> > >
> >
> >
> >

[Nuraq]

Andy,

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

<Andy> wrote in message news:%[email protected]...
>I missed your worksheet ref:
> =SUMPRODUCT(--(ISNUMBER(myworksheet!A2:A500)))
>
>
> <Andy> wrote in message news:%[email protected]...
>> Hi
>>
>> Try this:
>> =SUMPRODUCT(--(ISNUMBER(A2:A500)))
>>
>> Andy.
>>
>> "cestbarb" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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.
>>
>>
>
>

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
here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Read the FORMAT OF SUMPRODUCT section about halfway through.

Andy.

"Nuraq" <[email protected]> wrote in message
news:[email protected]...
> Andy,
>
> What is the purpose of the "--" before the ISNUMBER?
>
> <Andy> wrote in message news:%[email protected]...
>>I missed your worksheet ref:
>> =SUMPRODUCT(--(ISNUMBER(myworksheet!A2:A500)))
>>
>>
>> <Andy> wrote in message news:%[email protected]...
>>> Hi
>>>
>>> Try this:
>>> =SUMPRODUCT(--(ISNUMBER(A2:A500)))
>>>
>>> Andy.
>>>
>>> "cestbarb" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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.
>>>
>>>
>>
>>
>
>