Formula- Count of items that meet a specific range criteria
 

Hello Dear Friends,

In desperate help.

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 ?

Any help will be greatly appreciated.


Regards,
Dumbo

Formula- Count of items that meet a specific range criteria
 

"Excel Dumbo" wrote:
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))

Try this in cell B2 for one way.

Personally rather than hard code the percentages into the formula I would split column A into two and have a low and high percentage then reference those cells in the formula. This would make it easier to update if the percentages change for any reason.

For the 119% and above you would just use a COUNTIF.

Perhaps you could use the FREQUENCY function ?

Formula- Count of items that meet a specific range criteria
 

"arthurbr" wrote:
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 A17:D25 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.

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

thanks joeu for your detailed explaination. Ur explntn makes it look simple and easy to follow