CountIf Function

[Guest]

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.

 

[Bondi]

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.

Hi Barb,

Maybe you can use the Sumproduct()

Something along the lines of:

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

Regards,
Bondi

 

[Guest]

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

 

[Guest]

Doesn't the sumproduct function ADD the values, not count them?

 

[Bondi]

Doesn't the sumproduct function ADD the values, not count them?

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

 

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

 

[Guest]

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

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

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.

 

[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

 

[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

<11
FORMULA
5 =COUNTIF($A$6:$A$15,A17)
10 =COUNTIF($A$6:$A$15,A18)
5 =+A20-A1

 

[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

 

[Maistrye]

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

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

 

[excesspotential]

Outstanding:

 

[tz81c1]

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