CountIf
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 |
CountIf
Two ways:
0-1% =SUMPRODUCT(($B$2:$B$13o%)*($B$2:$B$13<=1%)) 1% - 2% and all others: =SUMPRODUCT(($B$2:$B$131%)*($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$13C1)*($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 Replace @mailinator.com with @tiscali.co.uk "carl" wrote in message ... 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 |
CountIf
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)) -- Don Guillett Microsoft MVP Excel SalesAid Software "carl" wrote in message ... 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 |
All times are GMT +1. The time now is 10:15 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com