Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#3
|
|||
|
|||
Quote:
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. |
#4
|
|||
|
|||
Perhaps you could use the FREQUENCY function ?
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
|
|||
|
|||
THank you very much. Each of your solutions worked like magic. appreciate your help.
|
#7
|
|||
|
|||
thanks joeu for your detailed explaination. Ur explntn makes it look simple and easy to follow
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count the number of items that meet 2 criteria? | Excel Worksheet Functions | |||
Sum of items in a column if they meet two criteria in another colu | Excel Worksheet Functions | |||
How do I get the total number of items that meet 2 criteria in Exc | Excel Worksheet Functions | |||
Count items when specific text and date criteria are met | Excel Worksheet Functions | |||
Formula that only adds numbers that meet specific criteria | Excel Discussion (Misc queries) |