Countif 1 column value is bigger than others

Hi ansd welcome to the forum

for the Bob-Roy match, use this...
=SUMPRODUCT(--(A2:A9>B2:B9))

If you want to compare them all against each other, try this...
1st, create a range name for each person (easily done by highlighting the entire range, select Formulas tab/Create from selection/check Top Row)
Then create a table like this...

	A	B	C	D	E
13		Bob	Roy	Ted	Eve
14	Bob		5	5	6
15	Roy	2		5	6
16	Ted	1	2		4
17	Eve	1	1	2

Copy this down and across...
=IF($A14=B$13,"",SUMPRODUCT(--(INDIRECT($A14)>INDIRECT(B$13))))

Thanks, that works.

I get the A2:A9>B2:B9 bit, but the dashes must mean something, what is it?

Thanks for the round-robin H2H scores, but I'm really after how many times each person won that 'row', or session.

the -- force a text number to be a number. the 1st 1 forces the conversion (but makes it negative), the 2nd 1 changes the neg to a pos

Originally Posted by jdjp298

Thanks for the round-robin H2H scores, but I'm really after how many times each person won that 'row', or session.

Try something like this

	A	B	C	D	E	F	G	H	I
1	Bob	Roy	Ted	Eve		Bob	Roy	Ted	Eve
2	12	11	9	15		4	3	2	2
3	15	12
4	9	13		5
5	25	3	15
6	15	15	15	15
7	9	12	9
8	10	9	8
9	10	9	11

Formula in F2 copied across
=SUMPRODUCT(--(A$2:A$9=SUBTOTAL(4,OFFSET($A$2:$D$9,ROW($A$2:$D$9)-ROW($A$2),0,1))))

Hope this helps

M.

Originally Posted by jdjp298

Marcelo

Thanks for the help, but it appears to count a draw as a win also. Can it be modified to be greater than, rather than greater than or equal? As it stands I have never used any of those commands before so don't really know what any of them are doing.

Maybe this...

F2
=SUMPRODUCT(--(A$2:A$9=SUBTOTAL(4,OFFSET($A$2:$D$9,ROW($A$2:$D$9)-ROW($A$2),0,1))),--(COUNTIF(OFFSET($A$2:$D$9,ROW($A$2:$D$9)-ROW($A$2),0,1),N(OFFSET(A$2:A$9,ROW(A$2:A$9)-ROW(A$2),0,1)))=1))

copy across

If you may fill in the blank cells with 0 you may use:

=SUMPRODUCT(--(COUNTIF(OFFSET($A$2:$D$9,ROW($B$2:$D$9)-MIN(ROW($B$2:$D$9)),0,1),"<"&A2:A9)=3))

array-entered to return total for Bob, then fill across three more cells.