CountIF or Sum IF Questin

CountIF or Sum IF Questin

Post by QmVuRjEyMz » Mon, 25 Aug 2008 07:46:01


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

Post by smarti » Mon, 25 Aug 2008 08:20:58


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

Post by V2lna » Mon, 25 Aug 2008 08:26:01

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

Post by TWF4 » Mon, 25 Aug 2008 08:34:01

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/
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---
 
 
 

CountIF or Sum IF Questin

Post by QmVuRjEyMz » Mon, 25 Aug 2008 09:30:00

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

Post by TWF4 » Mon, 25 Aug 2008 16:34:01

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/
Downloads:17,400 Files:358 Subscribers:55
xdemechanik
---