Countif >50<60

[jngil]

Please ...
In a list of cells with a mixed range of values, I use the "COUNTIF
functin to count values ">50", for example, but I'de like to have
count of values from 50 to 60. Does anyone kows how to use COUNTIF t
count a range of values ?
Thanks

 

[Frank Kabel]

Hi
one way:
=COUNTIF(A1:A100,">50")-COUNTIF(A1:A100,">=60")

or try
=SUMPRODUCT(--(A1:a100>50),--(A1:A100<60))

 

[jngil]

Thanks for your suggestions ! It DOES work ... and resolve my problem !

The first one:
=COUNTIF(A1:A10,">50")-COUNTIF(A1:A10,">=60"),
but the second
=SUMPRODUCT(--(A1:A10>50),--(A1:A10<60))
has a zero result ...

By the way: what means the two minus signals "--" ?
Gi

 

[Jerry W. Lewis]

A1:A10>50 is an array of logical variables (True or False), which would
be ignored by SUMPRODUCT. The minus sign coerces them to numbers, but
would change True to -1; the second minus makes it positive so that
summing would be equivalent to counting. You could simplify to

=SUMPRODUCT((A1:A10>50)*(A1:A10<60)) since the explicit multiplication
would also coerce to numbers without the sign issue.

The COUNTIF() and either SUMPRODUCT() formula should produce exactly the
same results. Copying them directly from your post into Excel, I was
unable to find any data where they diverged. Are you sure that you
entered them correctly when you got different results? What data was in
A1:A10?

Jerry

 

[Frank Kabel]

Hi
both formulas should return the same result. Some ideas:
- you have to include the double minus signs. Don't forget them
- What is the exact formula you have used?

Also see for an explanation:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

 

[jngil]

Thank you very much *Frank Kabel* and *Jerry W. Lewis*
You are right ! BOTH formulas produce the same result.
= SUMPRODUCT((A1:A10>50)*(A1:A10<60))
= COUNTIF(A1:A10,">50")-COUNTIF(A1:A10,">=60")
So, I've just finished my work.

I appreciate very much you availability.

Kind regards,
Gil
Porto, Portuga