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!!