Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
countif | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |