Formula- Count of items that meet a specific range criteria

[Ecel Dumbo]

Hello Dear Friends,


In desparate 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.


http://speedy.sh/HkZwV/Count-of-items-that-specify-a-range-criteria.xls


Regards,

Dumbo

 

[Hui]

Dumbo


Change A2:A8 to

[pre]

72%
85%
99%
100%
110%
119%
800%

[/pre]
Then in B2: =COUNTIFS(B$17:B$25,">="&$A2,B$17:B$25,"<"&$A3)

Copy across and down

 

[Faseeh]

Hi Ecel Dumbo,


I rearranged your like bellow:

[pre]

72%	85%
85%	90%
90%	100%
100%	110%
110%	118%
119%[/pre]
This is present in cells A2:B7 as percentages: use this formula:


=SUMPRODUCT((C17:C25>=$A2)*(C17:C25<=$B2)*1)

Drag to right/down


Regards,

 

[Ecel Dumbo]

Thanks a Million Hui and Faseeh


Worked like Magic. Hats off.

 

[mgesh2002]

@Dumbo,


You can try this formula to get the result, without rearranging your data.


=COUNTIFS(B$17:B$25,">="&LEFT($A2,FIND("%",$A2,1)-1)&"%",B$17:B$25,"<"&LEFT($A3,FIND("%",$A3,1)-1)&"%")


Just type ">800%" at cell "A8"