Countif

H

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
 
B

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")
 
H

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?
 
B

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))
 
H

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.
 
B

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)
 
B

bpeltzer

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

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.
 
Top