Formula- Count of items that meet a specific range criteria

[Excel Dumbo]

Hello Dear Friends,

In desperate help.

Please refer attached excel sheet below. Could you please suggest
formula to get the count of items that fall in a specific data range ?

Any help will be greatly appreciated.


Regards,
Dumb

+-------------------------------------------------------------------
|Filename: Count of items that specify a range criteria.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=705
+-------------------------------------------------------------------

 

[joeu2004]

Please refer attached excel sheet below. Could you please
suggest a formula to get the count of items that fall in
a specific data range ? [....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=705|

In would be better if you inserted a column to the left of the current
column A and entered the lower percentages into the new column A; that is,
72%, 85%, 90%, 100%, 110%, 119%.

Then your quarterly data are now in columns C, D and E.

Enter the following formula into C2:

=COUNTIF(C$17:C$25,">="&$A2)-COUNTIF(C$17:C$25,">="&$A3)

Copy C2 and paste-special-formula into C3:C6. Enter the following formula
into C7:

=COUNTIF(C$17:C$25,">="&$A7)

Copy C2:C7 and paste-special-formula into D2:E7.

Note: If you prefer, use the following formula instead of the first
COUNTIF-COUNTIF formula:

=SUMPRODUCT((C$17:C$25>=$A2)*(C$17:C$25<$A3))

 

[arthurbr]

Hello Dear Friends,

In desperate help.

Please refer attached excel sheet below. Could you please suggest
formula to get the count of items that fall in a specific data range ?

Any help will be greatly appreciated.


Regards,
Dumbo

Perhaps you could use the FREQUENCY function

+-------------------------------------------------------------------
|Filename: Count of items that specify a range criteria.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=706
+-------------------------------------------------------------------

 

[joeu2004]

Perhaps you could use the FREQUENCY function ? [....]
|Download: http://www.excelbanter.com/attachment.php?attachmentid=706|

I concur. However, I disagree with your implementation.

First, I think the "bin" values in column A should be "Excel Dumb's" lower
limits, to wit: 72%, 85%, 90%, 100%, 110%, 119%.

(So there are 6 "bins" as "Excel Dumb" had, not 7 as "arthurbr" has.)

Second, I think the FREQUENCY "bins" range (2nd parameter) should be
$A$2:$A$6, excluding A7, even though the FREQUENCY formula is array-entered
into B2:B7 [1].

That is, there is no need for an "above" bin. The 119% bin will act as
such.

Note that "Excel Dumb" was wrong to label his ranges with an upper bound,
especially the second-to-last range labeled "110% - 118%". In "Excel
Dumb's" example, all of the percentage in A1725 are constants. But if
they are computed (probably the case in real-life), 118% is not the
next-lower percentage below 119%.

-----
[1] @"Excel Dumb".... Note that the FREQUENCY formula is array-entered.
You do that by selecting B2:B7, typing the formula
=FREQUENCY(B17:B25,$A$2:$A$6), then pressing ctrl+shift+Enter instead of
just Enter. If you need to edit the formula later, you will probably need
to select B2:B7, edit and delete the formula, then presss ctrl+shift+Enter
to re-array-enter it.

 

[Excel Dumbo]

THank you very much. Each of your solutions worked like magic
appreciate your help

+-------------------------------------------------------------------
+-------------------------------------------------------------------