## CountIF or Sum IF Questin

### CountIF or Sum IF Questin

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

### CountIF or Sum IF Questin

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

### CountIF or Sum IF Questin

Hi

If you concatenate columns A and B, you can suffice with a simple COUNTIF
function.

--
Wigi
http://www.yqcomputer.com/ = Excel/VBA, soccer and music

### CountIF or Sum IF Questin

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://www.yqcomputer.com/
xdemechanik
---

### CountIF or Sum IF Questin

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

### CountIF or Sum IF Questin

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://www.yqcomputer.com/