CountIF or Sum IF Questin

  • Thread starter Thread starter BenF12345
  • Start date Start date
B

BenF12345

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