CountIf

[carl]

my data look like this:

Date MS%
3-Jan 6.16%
4-Jan 5.87%
5-Jan 2.60%
8-Jan 5.89%
9-Jan 3.80%
10-Jan 4.42%
11-Jan 5.34%
12-Jan 5.49%
16-Jan 4.00%
17-Jan 5.28%
18-Jan 4.84%
19-Jan 5.26%


I am trying to find a formula for the table below (count the number of time
colB above is between 0 and 1%, 1 and 2%, 3 and 4%

0 to 1% 1 to 2% 2 to 3%
0 0 1


Thank you in advance

 

[Sandy Mann]

Two ways:

0-1%
=SUMPRODUCT(($B$2:$B$13>o%)*($B$2:$B$13<=1%))

1% - 2% and all others:
=SUMPRODUCT(($B$2:$B$13>1%)*($B$2:$B$13<=2%))
change the % to suit

If you have a list of the percentages in say C1: C20 with 0% to 20% then you
can make the formula:
=SUMPRODUCT(($B$2:$B$13>C1)*($B$2:$B$13<=C2))
and drag the formula along

Other way:

0-1%
=COUNTIF(B2:B13,"<="&C1)

1% - 2%
=COUNTIF($B$2:$B$13,"<"&C2)-SUM($A$17:A17)

and format as General or Number

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Don Guillett]

try this idea copied down for the second and beyond
=SUMPRODUCT(($M$2:$M$14>=ROW(A1)/100)*($M$2:$M$14<ROW(A2)/100))