Countif
 

I'm trying to add a formula that will count the number of occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or equal to 10.

Any suggestions or is there another function that would work better?

TIA

Countif
 

Countif will only take one criterion. But if you just want a range, you
could count everything =1 and subtract everything 10. What's left will be
the number in your range.
=countif(a1:a100,"=1")-countif(a1:a100,"10")

"HJ" wrote:

I'm trying to add a formula that will count the number of occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or equal to 10.

Any suggestions or is there another function that would work better?

TIA

Countif
 

This would give me a number much higher than I am looking for since my range
has numbers that go up to 50 (and I'm actually looking to identify the number
of occurences that fall between 7 and 13. Is there another function that
would allow me to use multiple criteria?

"bpeltzer" wrote:

Countif will only take one criterion. But if you just want a range, you
could count everything =1 and subtract everything 10. What's left will be
the number in your range.
=countif(a1:a100,"=1")-countif(a1:a100,"10")

"HJ" wrote:

I'm trying to add a formula that will count the number of occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or equal to 10.

Any suggestions or is there another function that would work better?

TIA

Countif
 

I'm not at all clear why this would give you too high a number.
=countif(a1:a100,"=7")-countif(a1:a100,"13") should count occurences
between 7 and 13 inclusive.
An alternate approach that supports multiple distinct criteria (as opposed
to a range of a single variable) is to use summproduct:
=sumproduct(--(a1:a100=7),--((a1:a100<=13))


"HJ" wrote:

This would give me a number much higher than I am looking for since my range
has numbers that go up to 50 (and I'm actually looking to identify the number
of occurences that fall between 7 and 13. Is there another function that
would allow me to use multiple criteria?

"bpeltzer" wrote:

Countif will only take one criterion. But if you just want a range, you
could count everything =1 and subtract everything 10. What's left will be
the number in your range.
=countif(a1:a100,"=1")-countif(a1:a100,"10")

"HJ" wrote:

I'm trying to add a formula that will count the number of occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or equal to 10.

Any suggestions or is there another function that would work better?

TIA

Countif
 

Here is my sample set of data:

1
4
11
3
10
1
(6)
4
8
1
8

The first part of the formula counts all numbers =7 (result is 4).
The second part of the formula counts all <=13 (result is 11).
The net result is (7) or 7 if I flip the formula.
The answer I am looking for is all numbers between 7 and 13 which is 4.

"bpeltzer" wrote:

I'm not at all clear why this would give you too high a number.
=countif(a1:a100,"=7")-countif(a1:a100,"13") should count occurences
between 7 and 13 inclusive.
An alternate approach that supports multiple distinct criteria (as opposed
to a range of a single variable) is to use summproduct:
=sumproduct(--(a1:a100=7),--((a1:a100<=13))


"HJ" wrote:

This would give me a number much higher than I am looking for since my range
has numbers that go up to 50 (and I'm actually looking to identify the number
of occurences that fall between 7 and 13. Is there another function that
would allow me to use multiple criteria?

"bpeltzer" wrote:

Countif will only take one criterion. But if you just want a range, you
could count everything =1 and subtract everything 10. What's left will be
the number in your range.
=countif(a1:a100,"=1")-countif(a1:a100,"10")

"HJ" wrote:

I'm trying to add a formula that will count the number of occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or equal to 10.

Any suggestions or is there another function that would work better?

TIA

Countif
 

=countif(a1:a100,"=7")-countif(a1:a100,"13")

does exactly that. Try it, I just did, it works.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"HJ" wrote in message
...
Here is my sample set of data:

1
4
11
3
10
1
(6)
4
8
1
8

The first part of the formula counts all numbers =7 (result is 4).
The second part of the formula counts all <=13 (result is 11).
The net result is (7) or 7 if I flip the formula.
The answer I am looking for is all numbers between 7 and 13 which is 4.

"bpeltzer" wrote:

I'm not at all clear why this would give you too high a number.
=countif(a1:a100,"=7")-countif(a1:a100,"13") should count occurences
between 7 and 13 inclusive.
An alternate approach that supports multiple distinct criteria (as
opposed
to a range of a single variable) is to use summproduct:
=sumproduct(--(a1:a100=7),--((a1:a100<=13))


"HJ" wrote:

This would give me a number much higher than I am looking for since my
range
has numbers that go up to 50 (and I'm actually looking to identify the
number
of occurences that fall between 7 and 13. Is there another function
that
would allow me to use multiple criteria?

"bpeltzer" wrote:

Countif will only take one criterion. But if you just want a range,
you
could count everything =1 and subtract everything 10. What's left
will be
the number in your range.
=countif(a1:a100,"=1")-countif(a1:a100,"10")

"HJ" wrote:

I'm trying to add a formula that will count the number of
occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or
equal to 10.

Any suggestions or is there another function that would work
better?

TIA

Countif
 

The second part yields 0 if you maintain the inequality I suggested (13, not
<=13). Then 4-0=4, your desired result.
--Bruce

"HJ" wrote:

Here is my sample set of data:

1
4
11
3
10
1
(6)
4
8
1
8

The first part of the formula counts all numbers =7 (result is 4).
The second part of the formula counts all <=13 (result is 11).
The net result is (7) or 7 if I flip the formula.
The answer I am looking for is all numbers between 7 and 13 which is 4.

"bpeltzer" wrote:

I'm not at all clear why this would give you too high a number.
=countif(a1:a100,"=7")-countif(a1:a100,"13") should count occurences
between 7 and 13 inclusive.
An alternate approach that supports multiple distinct criteria (as opposed
to a range of a single variable) is to use summproduct:
=sumproduct(--(a1:a100=7),--((a1:a100<=13))


"HJ" wrote:

This would give me a number much higher than I am looking for since my range
has numbers that go up to 50 (and I'm actually looking to identify the number
of occurences that fall between 7 and 13. Is there another function that
would allow me to use multiple criteria?

"bpeltzer" wrote:

Countif will only take one criterion. But if you just want a range, you
could count everything =1 and subtract everything 10. What's left will be
the number in your range.
=countif(a1:a100,"=1")-countif(a1:a100,"10")

"HJ" wrote:

I'm trying to add a formula that will count the number of occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or equal to 10.

Any suggestions or is there another function that would work better?

TIA

Countif
 

Got it. Thanks for your patience. It's been a long day already and I'm
obviously not thinking clearly.

Thanks for the sumproduct formula also. I thought that function might work
but couldn't get it exactly right.

"bpeltzer" wrote:

The second part yields 0 if you maintain the inequality I suggested (13, not
<=13). Then 4-0=4, your desired result.
--Bruce

"HJ" wrote:

Here is my sample set of data:

1
4
11
3
10
1
(6)
4
8
1
8

The first part of the formula counts all numbers =7 (result is 4).
The second part of the formula counts all <=13 (result is 11).
The net result is (7) or 7 if I flip the formula.
The answer I am looking for is all numbers between 7 and 13 which is 4.

"bpeltzer" wrote:

I'm not at all clear why this would give you too high a number.
=countif(a1:a100,"=7")-countif(a1:a100,"13") should count occurences
between 7 and 13 inclusive.
An alternate approach that supports multiple distinct criteria (as opposed
to a range of a single variable) is to use summproduct:
=sumproduct(--(a1:a100=7),--((a1:a100<=13))


"HJ" wrote:

This would give me a number much higher than I am looking for since my range
has numbers that go up to 50 (and I'm actually looking to identify the number
of occurences that fall between 7 and 13. Is there another function that
would allow me to use multiple criteria?

"bpeltzer" wrote:

Countif will only take one criterion. But if you just want a range, you
could count everything =1 and subtract everything 10. What's left will be
the number in your range.
=countif(a1:a100,"=1")-countif(a1:a100,"10")

"HJ" wrote:

I'm trying to add a formula that will count the number of occurences between
two criteria. How do you add another criteria to this formula?

countif(a1:a100,"=1") I would also like to say and less than or equal to 10.

Any suggestions or is there another function that would work better?

TIA