FREQUENCY and COUNTIF

  • Thread starter Thread starter Syed Zeeshan Haider
  • Start date Start date
S

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(D10:D94,">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(D10:D94,"where value is between 100 and 150")?

Thank you,
 
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
 
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
 
Clarification? Is 100 "between" 100 and 150? How about 150?

Alan Beban
Click to expand...

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,
 
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?
Click to expand...

Yes! I got the expected results. Thank you!
If not get back to us
after reading what Help has to say about FREQUENCY.
Click to expand...

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")
Click to expand...

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 )
Click to expand...

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!
 
Syed said:
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!
Click to expand...

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