counting functions
 

Hello,
I am trying to get a function to calculate for me a group of numbers.
Example: I have in one column 38, 39, 37, 39. I want to know if there is a
function that I can use that will calculate out the amount of 39 & 37. (the
answer I am looking for would be 3). Please help. thanks, joe

One way:

=SUMPRODUCT(--(A516:A519=37)) + SUMPRODUCT(--(A516:A519=39))

"jcrown" wrote in message
...
Hello,
I am trying to get a function to calculate for me a group of numbers.
Example: I have in one column 38, 39, 37, 39. I want to know if there is
a
function that I can use that will calculate out the amount of 39 & 37.
(the
answer I am looking for would be 3). Please help. thanks, joe

Assume the numbers are in col A, in A1 down

Put in say, B1: =SUMPRODUCT((A1:A100=37)+(A1:A100=39))

Adapt the ranges to suit, but note that entire col references (A:A, B:B,
etc) cannot be used in SUMPRODUCT
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"jcrown" wrote in message
...
Hello,
I am trying to get a function to calculate for me a group of numbers.
Example: I have in one column 38, 39, 37, 39. I want to know if there is
a
function that I can use that will calculate out the amount of 39 & 37.
(the
answer I am looking for would be 3). Please help. thanks, joe

Try
=countif(range,37)+countif(range,39)

"jcrown" wrote:

Hello,
I am trying to get a function to calculate for me a group of numbers.
Example: I have in one column 38, 39, 37, 39. I want to know if there is a
function that I can use that will calculate out the amount of 39 & 37. (the
answer I am looking for would be 3). Please help. thanks, joe

or a bit simpler

=SUMPRODUCT(--(A1:A100={37,39}))

--
HTH

Bob Phillips

"Max" wrote in message
...
Assume the numbers are in col A, in A1 down

Put in say, B1: =SUMPRODUCT((A1:A100=37)+(A1:A100=39))

Adapt the ranges to suit, but note that entire col references (A:A, B:B,
etc) cannot be used in SUMPRODUCT
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"jcrown" wrote in message
...
Hello,
I am trying to get a function to calculate for me a group of numbers.
Example: I have in one column 38, 39, 37, 39. I want to know if there
is
a
function that I can use that will calculate out the amount of 39 & 37.
(the
answer I am looking for would be 3). Please help. thanks, joe

Some options...

=SUM(COUNTIF(Range,{37,39}))

=SUMPRODUCT(ISNUMBER(MATCH(Range,{37,39},0))+0)

If X2:Y2 houses the criterion numbers (i.e., 37, 39)...

=SUMPRODUCT(ISNUMBER(MATCH(Range,X2:Y2,0))+0)

jcrown wrote:
Hello,
I am trying to get a function to calculate for me a group of numbers.
Example: I have in one column 38, 39, 37, 39. I want to know if there is a
function that I can use that will calculate out the amount of 39 & 37. (the
answer I am looking for would be 3). Please help. thanks, joe