Lookup & countif

I have data as below:
31-May-11 -3.8%
31-Aug-11 -3.5%
30-Jun-10 -2.8%
30-Nov-10 -2.8%
30-Jun-11 -2.5%
31-Mar-11 -2.3%
31-Jan-10 -1.6%
31-Jan-11 -1.1%
30-Apr-10 -1.0%
30-Nov-11 -0.9%
31-Dec-11 -0.9%
31-Jan-12 -0.7%
31-Mar-12 0.2%
31-Oct-10 0.7%
29-Feb-12 0.8%
30-Sep-11 1.0%
31-Dec-10 2.3%
31-Oct-11 2.3%
31-Mar-10 3.0%
31-Aug-10 4.1%
31-Jul-10 4.9%
30-Apr-11 5.8%
30-Sep-10 6.9%
28-Feb-10 9.1%

I want to lookup the range as below & do a countif function.

-10% to -9%
-9% to -8%
-8% to -7%
-7% to -6%
-6% to -5%
-5% to -4%

Please help

Hi Babu,

Please find attached.

Quote:

---

Originally Posted by Admin

Hi Babu,

Please find attached.

---

The formula you used in your attachment was this...

=SUMPRODUCT(--($B$2:$B$25>=E2),--($B$2:$B$25<F2))

An alternate formula that will produce the same results is this...

=COUNTIF(B$2:B$25,"<"&F2)-COUNTIF(B$2:B$25,"<"&E2)

I am not sure which of these two formulas would be more efficient. In my experience, the SUMPRODUCT function tends to be slow because, underneath it all, it is actually an array function that does not require using CTRL+SHIFT+ENTER to enable the array; however, making two COUNTIF function calls against the same data set might also be slow... I am just not sure.

Both formuales work very well.

Thanks