How do I find out how many times Group 1,2,3 used each test? Using Excel 2000 A B C D E F Group Test Group Test 1 Test 2 Test 3 1 Test 1 1 2 Test 3 2 3 Test 2 3 1 Test 1 2 Test 3 3 Test 3 Thanks!!!
BenF12345 wrote: > How do I find out how many times Group 1,2,3 used each test? > Using Excel 2000 > > A B C D E F > Group Test Group Test 1 Test 2 Test 3 > 1 Test 1 1 > 2 Test 3 2 > 3 Test 2 3 > 1 Test 1 > 2 Test 3 > 3 Test 3 > > > Thanks!!! In D2, then fill right and down. This is an array formula (commit with Ctrl+Shift+Enter). =SUM(($C2=$A$2:$A$7)*(D$1=$B$2:$B$7))
Hi If you concatenate columns A and B, you can suffice with a simple COUNTIF function. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "BenF12345" wrote: > How do I find out how many times Group 1,2,3 used each test? > Using Excel 2000 > > A B C D E F > Group Test Group Test 1 Test 2 Test 3 > 1 Test 1 1 > 2 Test 3 2 > 3 Test 2 3 > 1 Test 1 > 2 Test 3 > 3 Test 3 > > > Thanks!!!
In D2: =SUMPRODUCT(($B$2:$B$10=D$1)*($A$2:$A$10=$C2)) Copy across/fill down to populate. Adapt the ranges to suit. Another alternative is to create a pivot on the source data in cols A & B, with "Group" placed in ROW area, "Test" in COLUMN area and in DATA area (Count of Test). -- Max Singapore http://savefile.com/projects/236895 Downloads:17,400 Files:358 Subscribers:55 xdemechanik --- "BenF12345" wrote: > How do I find out how many times Group 1,2,3 used each test? > Using Excel 2000 > > A B C D E F > Group Test Group Test 1 Test 2 Test 3 > 1 Test 1 1 > 2 Test 3 2 > 3 Test 2 3 > 1 Test 1 > 2 Test 3 > 3 Test 3 > > > Thanks!!!
Thanks!! In trying to generalize I kind of screwed up my question. What if the name sof the tests were embedded with other text in that cell. John's Test 1, Ken's Test 1, Jane's Test 3 etc... I still need to count the number of Test 1s', but I can't do it by saying it will equal the heading in column D. Thanks for the help. You guys are great!! "Max" wrote: > In D2: > =SUMPRODUCT(($B$2:$B$10=D$1)*($A$2:$A$10=$C2)) > Copy across/fill down to populate. Adapt the ranges to suit. > > Another alternative is to create a pivot on the source data in cols A & B, > with "Group" placed in ROW area, "Test" in COLUMN area and in DATA area > (Count of Test). > -- > Max > Singapore > http://savefile.com/projects/236895 > Downloads:17,400 Files:358 Subscribers:55 > xdemechanik > --- > "BenF12345" wrote: > > How do I find out how many times Group 1,2,3 used each test? > > Using Excel 2000 > > > > A B C D E F > > Group Test Group Test 1 Test 2 Test 3 > > 1 Test 1 1 > > 2 Test 3 2 > > 3 Test 2 3 > > 1 Test 1 > > 2 Test 3 > > 3 Test 3 > > > > > > Thanks!!!
If col B contains the embedded data as you mentioned below, then you could try this instead, in D2: =SUMPRODUCT((ISNUMBER(SEARCH(D$1,$B$2:$B$10)))*($A$2:$A$10=$C2)) Copy D2 across/fill down to populate. Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,400 Files:358 Subscribers:55 xdemechanik --- "BenF12345" wrote: > Thanks!! In trying to generalize I kind of screwed up my question. What if > the name sof the tests were embedded with other text in that cell. John's > Test 1, Ken's Test 1, Jane's Test 3 etc... > I still need to count the number of Test 1s', but I can't do it by saying it > will equal the heading in column D. > > Thanks for the help. You guys are great!!
