CountIf Function

B

Barb Miles

I have a range of numbers between 1 and 25 in 50 cells.

How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?

Or if there is another function to use, let me know.

Thank you.
 
B

Bondi

Barb said:
I have a range of numbers between 1 and 25 in 50 cells.

How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?

Or if there is another function to use, let me know.

Thank you.
Click to expand...

Hi Barb,

Maybe you can use the Sumproduct()

Something along the lines of:

=SUMPRODUCT(--(A1:A50>5),--(A1:A50<10))

Regards,
Bondi
 
B

bpeltzer

Use countif twice, once to count those that are >=5 and a second time to
count those that are >10. The difference is the count of those in the range
[5,10]:
=countif(range,">=5")-countif(range,">10")
 
B

Bondi

Barb said:
Doesn't the sumproduct function ADD the values, not count them?
Click to expand...

Hi,
Well it does sum the products. But in this case the products will be 1
when both conditions are met and 0 if none or only one of the
conditions are met. So it will sum all the 1's where both conditions
are met and hence function somthing like a counting function.

Regards,
Bondi
 
P

patele

Enter the following

=SUM((((A1:A20)<10)+((A1:A20))>5)*1)

After entering the above you need to press the Ctrl, Shift and Enter
keys to enter it as an array.
 
B

Barb Miles

This does not work. It returns an answer of one more than the answer should
be.
--
Barb


bpeltzer said:
Use countif twice, once to count those that are >=5 and a second time to
count those that are >10. The difference is the count of those in the range
[5,10]:
=countif(range,">=5")-countif(range,">10")

Barb Miles said:
I have a range of numbers between 1 and 25 in 50 cells.

How could I use the countif function to look at that range of numbers for
values between 5 and 10 and count those values that meet the criteria of
between 5 and 10?

Or if there is another function to use, let me know.

Thank you.
Click to expand...
Click to expand...
 
P

patele

Barb,

Don't know if you found the answer yet but here a formula that should
work for you. Let me know if it does.

=COUNTIF(A1:A50,">=5")-COUNTIF(A1:A50,">10")

Ed
 
S

Swaroon

Hi, how about something simple like :
Range is 1 to 10
criteria is '>5
criteria is '<11
Use one Countif to work out the answer for values over 5 in the range
Use one Countif to work out the answer for values less than 11
Answer 2 less answer 1 = correct number of values less greater than
but less than 11 - sorry, but it's a bit basic !

eg.
1
2
3
4
5
6
7
8
9
10
5
Click to expand...
<11
FORMULA
5 =COUNTIF($A$6:$A$15,A17)
10 =COUNTIF($A$6:$A$15,A18)
5 =+A20-A1
 
E

excesspotential

I have a similar issue.

I am trying to count two columns with different data.


=countif(E5:E10,"=F") and (F5:F10,"=X")
=countif(E5:E10,"=M") and (F5:F10,"=X")

I am not getting any results

Ji
 
M

Maistrye

excesspotential said:
I have a similar issue.

I am trying to count two columns with different data.


=countif(E5:E10,"=F") and (F5:F10,"=X")
=countif(E5:E10,"=M") and (F5:F10,"=X")

I am not getting any results

Jim
Click to expand...

Basically, just copy what Bondi stated up above:

=SUMPRODUCT(--(A1:A50>5),--(A1:A50<10))

except twist it into:

=SUMPRODUCT(--(E5:E10="F"),--(F5:F10="X"))
=SUMPRODUCT(--(E5:E10="M"),--(F5:F10="X"))

Scot
 
T

tz81c1

:cool: Yes I agree, OUTSTANDING! I've read many threads looking for
simple formula like this that works. Well Done
 
Top