Countif

[HJ]

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

[bpeltzer]

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

[HJ]

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

[bpeltzer]

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

[HJ]

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

[Bob Phillips]

=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" <HJ@discussions.microsoft.com> wrote in message
news:0A51A5F8-2610-4B5E-A710-CC2C38ABF7AC@microsoft.com...
> 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

[bpeltzer]

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

[HJ]

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