Countifs of appearance within the same Groups listed in a column

[dongjip]

Hi I am new here. I am wondering how to do countifs with different arrays but also counting by reference to groups within a column. For example in the table below, you will see Column A, there are four groups. How do I count the number of times in which John and Elaine appear in the same group and John ranks 1. In the example, the number of counts should be 2. Do I need VBA for this? My spreadsheets have about 400 groups.

Thanks!
DJ

Excel Workbook
	A	B	C
1	Group	Name	Rank
2	1	John	1
3	1	Pearce	2
4	1	Douglas	3
5	1	Elaine	4
6	1	Tim	6
7	2	John	1
8	2	Tim	2
9	2	Pearce	3
10	2	Elaine	4
11	2	Oliver	5
12	3	Tim	1
13	3	John	2
14	3	Oliver	3
15	3	Elaine	4
16	3	Pearce	5
17	4	John	1
18	4	Pearce	2
19	4	Tim	3
20	4	Toby	4
Sheet1

 

[arthurbr]

Perhaps = sumproduct((a2:a20=1)*(b2:b20="John"))

A Pivot Table might also help in this case

 

[dongjip]

Thanks for the quick reply. I have not explained clearly. What i needed to count is that out of the four groups in Column A, there are only three groups in which John and Elaine are within the same Group and out of those three groups, John has ranked 1 twice. My problem is I do not not know how to copy the counting for each Group in Column A.

 

[Aladin Akyurek]

Shouldn't the count 3, not 3, as john and elaine appear together in the 3 different groups together, i.e. group 1, 2, and 3?

 

[dongjip]

Thanks Aladin, The count should be 2 because although they are in the same group for 3 groups, John ranked "1" in only two of them (in column c).
DJ

 

[Aladin Akyurek]

Shouldn't the count 3, not 3, as john and elaine appear together in the 3 different groups together, i.e. group 1, 2, and 3?

Thanks Aladin, The count should be 2 because although they are in the same group for 3 groups, John ranked "1" in only two of them (in column c).
DJ

Right...

Control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(MATCH(IF($B$2:$B$20=E1,IF($C$2:$C$20=G1,$A$2:$A$20)),IF($B$2:$B$20=F1,$A$2:$A$20,"#"),0)),1))

where E1 = john, F1 = elaine, and G1 = 1.

 

[dongjip]

Thanks very much!

 

[Aladin Akyurek]

Thanks very much!

You are welcome.