Information from one column then filtered(?) from another Deborah S. 14.09.2006 01:43:01 Here is a brief example of what I am trying to accomplish: A B 1 Don 2 Jim w 3 Don w 4 Bob 5 Jim w I would like to count the number of times that Don's name is on the list, then the number of times Don has a "w" in the next column. The COUNTIF function works for the first one but how do I get the second amount? The results should be: Bob 1 Don 2 1 Jim 2 2 I thank any and all in advance for any help that can be given on this. Deborah

Re: Information from one column then filtered(?) from another Max 14.09.2006 02:00:02 Assuming the source data is within A1:B20, and names are listed in D1 down, viz: [Quoted Text] > Bob > Don > Jim etc Put in say, F1, and copy down: =SUMPRODUCT(($A$1:$A$20=D1)*($B$1:$B$20="w")) Adapt the ranges to suit. Note that sumproduct doesn't accept entire col references, eg: A:A, B:B -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Deborah S." wrote: > Here is a brief example of what I am trying to accomplish: > A B > 1 Don > 2 Jim w > 3 Don w > 4 Bob > 5 Jim w > > I would like to count the number of times that Don's name is on the list, > then the number of times Don has a "w" in the next column. The COUNTIF > function works for the first one but how do I get the second amount? > > The results should be: > Bob 1 > Don 2 1 > Jim 2 2 > > I thank any and all in advance for any help that can be given on this. > > Deborah

Re: Information from one column then filtered(?) from another "Biff" <biffinpitt[ at ]comcast.net> 14.09.2006 02:00:28 Hi! Try this: Names listed in D1:D3 - D1 = Bob D2 = Don D3 = Jim Formula in E1: =COUNTIF(A$1:A$5,D1) Formula in F1: =SUMPRODUCT(--(A$1:A$5=D1),--(B$1:B$5="w")) Select both E1 and F1 and copy down as needed. Biff "Deborah S." <Deborah S.[ at ]discussions.microsoft.com> wrote in message news:E9CA91AA-70E6-4B13-B22B-9F98F28E698D[ at ]microsoft.com... [Quoted Text] > Here is a brief example of what I am trying to accomplish: > A B > 1 Don > 2 Jim w > 3 Don w > 4 Bob > 5 Jim w > > I would like to count the number of times that Don's name is on the list, > then the number of times Don has a "w" in the next column. The COUNTIF > function works for the first one but how do I get the second amount? > > The results should be: > Bob 1 > Don 2 1 > Jim 2 2 > > I thank any and all in advance for any help that can be given on this. > > Deborah

Re: Information from one column then filtered(?) from another Deborah S. 14.09.2006 02:13:02 Thanks so much, worked great. "Biff" wrote: [Quoted Text] > Hi! > > Try this: > > Names listed in D1:D3 - > > D1 = Bob > D2 = Don > D3 = Jim > > Formula in E1: > > =COUNTIF(A$1:A$5,D1) > > Formula in F1: > > =SUMPRODUCT(--(A$1:A$5=D1),--(B$1:B$5="w")) > > Select both E1 and F1 and copy down as needed. > > Biff > > "Deborah S." <Deborah S.[ at ]discussions.microsoft.com> wrote in message > news:E9CA91AA-70E6-4B13-B22B-9F98F28E698D[ at ]microsoft.com... > > Here is a brief example of what I am trying to accomplish: > > A B > > 1 Don > > 2 Jim w > > 3 Don w > > 4 Bob > > 5 Jim w > > > > I would like to count the number of times that Don's name is on the list, > > then the number of times Don has a "w" in the next column. The COUNTIF > > function works for the first one but how do I get the second amount? > > > > The results should be: > > Bob 1 > > Don 2 1 > > Jim 2 2 > > > > I thank any and all in advance for any help that can be given on this. > > > > Deborah > > >

Re: Information from one column then filtered(?) from another "Biff" <biffinpitt[ at ]comcast.net> 14.09.2006 02:20:44 You're welcome. Thanks for the feedback! Biff "Deborah S." <DeborahS[ at ]discussions.microsoft.com> wrote in message news:6D2ECB77-2FB4-4B55-AAB0-F6AEF717B49E[ at ]microsoft.com... [Quoted Text] > Thanks so much, worked great. > > "Biff" wrote: > >> Hi! >> >> Try this: >> >> Names listed in D1:D3 - >> >> D1 = Bob >> D2 = Don >> D3 = Jim >> >> Formula in E1: >> >> =COUNTIF(A$1:A$5,D1) >> >> Formula in F1: >> >> =SUMPRODUCT(--(A$1:A$5=D1),--(B$1:B$5="w")) >> >> Select both E1 and F1 and copy down as needed. >> >> Biff >> >> "Deborah S." <Deborah S.[ at ]discussions.microsoft.com> wrote in message >> news:E9CA91AA-70E6-4B13-B22B-9F98F28E698D[ at ]microsoft.com... >> > Here is a brief example of what I am trying to accomplish: >> > A B >> > 1 Don >> > 2 Jim w >> > 3 Don w >> > 4 Bob >> > 5 Jim w >> > >> > I would like to count the number of times that Don's name is on the >> > list, >> > then the number of times Don has a "w" in the next column. The COUNTIF >> > function works for the first one but how do I get the second amount? >> > >> > The results should be: >> > Bob 1 >> > Don 2 1 >> > Jim 2 2 >> > >> > I thank any and all in advance for any help that can be given on this. >> > >> > Deborah >> >> >>

Re: Information from one column then filtered(?) from another Max 14.09.2006 02:46:02 hmm .. wonder why my sumproduct version didn't work for the OP <g> -- Max Singapore http://savefile.com/projects/236895 xdemechanik ---

Re: Information from one column then filtered(?) from another "Biff" <biffinpitt[ at ]comcast.net> 14.09.2006 02:58:12 For the same reason that my "Ticket Checker" sucks! <g> Biff "Max" <demechanik[ at ]yahoo.com> wrote in message news:98F337F3-E1AF-42C7-A1A7-9032D4AF8C68[ at ]microsoft.com... [Quoted Text] > hmm .. wonder why my sumproduct version didn't work for the OP <g> > > -- > Max > Singapore > http://savefile.com/projects/236895 > xdemechanik > ---

Re: Information from one column then filtered(?) from another "via135 via OfficeKB.com" <u23552[ at ]uwe> 16.09.2006 17:47:00 ha..ha..hha!! -via135 Max wrote: [Quoted Text] >hmm .. wonder why my sumproduct version didn't work for the OP <g> > -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-functions/200609/1