FREQUENCY and COUNTIF

[Syed Zeeshan Haider]

Hello Experts,
I have Excel97Pro on Win98SE.

I am trying to use FREQUENCY and COUNTIF functions but I am having trouble
with both of them. I have dug up deep into help files but the information
there is too limited.

I have an example in a statistics book which distributes some data into
frequency distribution table according to following criteria:
110-119, 120-129, 130-139, 140-149, 150-159 and so on, up to 210-219.

I have entered that data into an Excel file and I am trying to accomplish
this frequency distribution using FREQUENCY function but I am not getting
expected results. How to tell the formula about the above-mentioned
criteria?

Can I have two criteria in COUNTIF function?
I can get results easily when the formula is as simple as:
=COUNTIF(A2:A22,"=786")
or
=COUNTIF(D1094,">100")
etc.

But what if I want to give a double criteria; e.g. more than 100 and less
than 150?

Is there any syntax for between for such situations where I could tell the
formula
=COUNTIF(D1094,"where value is between 100 and 150")?

Thank you,

 

[Alan Beban]

Clarification? Is 100 "between" 100 and 150? How about 150?

Alan Beban

 

[Bernard V Liengme]

Let's say you put the data in A1:A100
Anywhere you like (let's say B1:B?) enter 119, 129, 139,149 .... 219
Select all the sell in column C next to this data; type
=FREQUENCY(A1:A100,B1:B??) and while holding down Shift and CTRL keys, tap
the Enter key. Do you get he expected results now? If not get back to us
after reading what Help has to say about FREQUENCY.

For multiple criteria you have two options
a) =COUNTIF(A1:A100,">150") - COUNTIF(A1:A100,"<100")
b) =SUMPRODUCT( (A1:A100>150)*(A1:A100<100)*1 )

Best wishes
Bernard

 

[Alan Beban]

In Bernard Liengme's first formula, the >150 should be <150.

In the second formula I get 0. I would not expect it to work in any
event since the two constituents being multiplied together will only
fortuitously be the same size; doesn't that matter?

Alan Beban

 

[Syed Zeeshan Haider]

Clarification? Is 100 "between" 100 and 150? How about 150?

Alan Beban

For a certain situation for the usage of COUNTIF, I'll prefer not to include
both 100 and 150. Being a student of mathematics, I'll use the rules of sets
where if "between" is used then the mentioned numbers are excluded.
Thanks,

 

[Syed Zeeshan Haider]

in message

Let's say you put the data in A1:A100
Anywhere you like (let's say B1:B?) enter 119, 129, 139,149 .... 219
Select all the sell in column C next to this data; type
=FREQUENCY(A1:A100,B1:B??) and while holding down Shift and CTRL keys, tap
the Enter key. Do you get he expected results now?

Yes! I got the expected results. Thank you!

If not get back to us
after reading what Help has to say about FREQUENCY.

But I still can't understand the more generalized rule for specifying the
criteria in FREQUENCY formula. Let's take the example of above-mentioned
situation. How did FREQUENCY understand the ranges of numbers with *just*
119, 129, 139,149 .... 219?

For multiple criteria you have two options
a) =COUNTIF(A1:A100,">150") - COUNTIF(A1:A100,"<100")

This option seems interesting. It means COUNTIF doesn't allow more than one
criteria! I believe I am going to use your approach to COUNTIF in future.
Thanks!

b) =SUMPRODUCT( (A1:A100>150)*(A1:A100<100)*1 )

Sorry, Bernard! It didn't work. Always returns "0" as result. But I became
aware to another function of Excel.


Your help is greatly appreciated. It was great deal of information and
learning.
Thanks again!

 

[Alan Beban]

in message


This option seems interesting. It means COUNTIF doesn't allow more than one
criteria! I believe I am going to use your approach to COUNTIF in future.
Thanks!

As I mentioned in an earlier post, for the OP's situation the >150
should be <150.

And the use of the above formula *does not* imply that COUNTIF doesn't
allow more than 1 criterion. It simply isn't clear what multiple
criteria is expected to return the OP's desired result.

The following, e.g., will work to return the sum of values less than 150
and values greater than 100:

=SUM(COUNTIF(A1:A100,{"<150",">100"}))

That just doesn't happen to be the logic that returns values between 100
and 50.

Alan Beban

 

[Alan Beban]

That just doesn't happen to be the logic that returns values between 100
and 50.

Or between 100 and 150

Alan Beban