counting values in a column
in [Functions]
|
From: Jerry on 2 Mar 2010 11:11 I put =countif(an2:an240,"<31") and it works correctly for these set of values or anything greater, in between values I have to do =countif(an2:an40,"<61")-whatever value I had before so if I have to breakdown into more groups i have to keep adding and substracting prior values so that my in between values are a pain in the neck to keep of track "pmartglass" wrote: > please explain what is not working correctly > the only thing that I see that may be a problem is how you are > handling situations like = 30 or = 60 > these situations will be excluded from your logic > if this is causing your problem you may just need to modify your starting or > ending points accordingly > > good luck > > > "Jerry" wrote: > > > I have different values in a a column that I need to count. > > Column A: 2,25,45,52,25,30,2,45,80,60 > > and I want to count values as follows > > <30 = 4 > > >30 and <60 = 4 > > >60 = 2 > > I have tried countif and sumproduct but it doesn't work correctly. Your > > assistance is greatly appreciated.
From: Fred Smith on 2 Mar 2010 12:48 Then you want Stefi's solution. Regards, Fred "Jerry" <Jerry(a)discussions.microsoft.com> wrote in message news:0C72969F-D1A0-4EBC-8806-7788DF6DE4D0(a)microsoft.com... >I put =countif(an2:an240,"<31") and it works correctly for these set of > values or anything greater, in between values I have to do > =countif(an2:an40,"<61")-whatever value I had before so if I have to > breakdown into more groups i have to keep adding and substracting prior > values so that my in between values are a pain in the neck to keep of > track > > "pmartglass" wrote: > >> please explain what is not working correctly >> the only thing that I see that may be a problem is how you are >> handling situations like = 30 or = 60 >> these situations will be excluded from your logic >> if this is causing your problem you may just need to modify your starting >> or >> ending points accordingly >> >> good luck >> >> >> "Jerry" wrote: >> >> > I have different values in a a column that I need to count. >> > Column A: 2,25,45,52,25,30,2,45,80,60 >> > and I want to count values as follows >> > <30 = 4 >> > >30 and <60 = 4 >> > >60 = 2 >> > I have tried countif and sumproduct but it doesn't work correctly. >> > Your >> > assistance is greatly appreciated.
From: T. Valko on 2 Mar 2010 13:08
I think you're not understanding what pmartglass pointed out. You posted these conditions: <30 = 4 >30 and <60 = 4 >60 = 2 Less than 30 Greater than 30 and less than 60 Greater than 60 So, with those intervals you're not counting 30 or 60. I'd use Stefi's suggestion but change the bins. Let's assume your data is in the range A2:A11. List the bins in C2:C4 - C2 = 29 C3 = 59 C4 = >59 Those are the bins (conditions ) you need based on your posted results: <30 = 4 >30 and <60 = 4 >60 = 2 Then, select the range D2:D4 Type this formula into the *formula bar*: =FREQUENCY(A2:A11,C2:C3) Do not hit Enter. Instead hold down both the CTRL key and the SHIFT key then hit Enter. -- Biff Microsoft Excel MVP "Jerry" <Jerry(a)discussions.microsoft.com> wrote in message news:0C72969F-D1A0-4EBC-8806-7788DF6DE4D0(a)microsoft.com... >I put =countif(an2:an240,"<31") and it works correctly for these set of > values or anything greater, in between values I have to do > =countif(an2:an40,"<61")-whatever value I had before so if I have to > breakdown into more groups i have to keep adding and substracting prior > values so that my in between values are a pain in the neck to keep of > track > > "pmartglass" wrote: > >> please explain what is not working correctly >> the only thing that I see that may be a problem is how you are >> handling situations like = 30 or = 60 >> these situations will be excluded from your logic >> if this is causing your problem you may just need to modify your starting >> or >> ending points accordingly >> >> good luck >> >> >> "Jerry" wrote: >> >> > I have different values in a a column that I need to count. >> > Column A: 2,25,45,52,25,30,2,45,80,60 >> > and I want to count values as follows >> > <30 = 4 >> > >30 and <60 = 4 >> > >60 = 2 >> > I have tried countif and sumproduct but it doesn't work correctly. >> > Your >> > assistance is greatly appreciated. |