Count Number of Instances of MAX across multiple columns

[RAyerst]

Hello all,

This seems relatively simple, but despite searching, I haven't found a workable solution, so grateful of any help

What I want is for each person to have a total of winning weeks, with a tie awarded to both. So the 'number of weekly wins' column would be populated with a formula that returns the number of occurrences that each player has had the maximum score in a given week:

Any help gratefully received, thanks

 

[Eric W]

Welcome to the MrExcel message board!

Try:

Book1
	A	B	C	D	E	F	G
1		Number of Weekly Wins	Week 1	Week 2	Week 3	Week 4	Week 5
2	Player 1	2	10	7	8	9	6
3	Player 2	2	8	10	8	6	5
4	Player 3	1	4	8	7	10	6
5	Player 4	2	5	6	8	7	8
Sheet5

Cell Formulas
Range	Formula
B2:B5	B2	=SUMPRODUCT(--(MMULT({1,1,1,1},--(C2:G2>=$C$2:$G$5))=4))

Note that both the array constant {1,1,1,1} and the constant on the end (4) are set up for 4 players. You'll need to change those if you have a different number of players, but I could adapt it to be more flexible if you want.

 

[RAyerst]

Thanks so much Eric - yes I would need to extend both number of players (maximum of 15) and number of weeks (maximum of 38) - am I correct in assuming there is a direct relationship between the array constant {1,1,1,1} the constant on the end (4), and the number of players (rows)? I would definitely be interested to know if there is a flexible way as you described to expand the formula.

I think looking at your formula, the number of weeks is easily changed through changing the range.

Thanks again for coming back so quickly, very helpful, and doesn't look like I was missing an obvious formula!

Rob

 

[Eric W]

I wouldn't say you missed anything obvious - MMULT is a great function, but most people don't remember their high school algebra, and even if they did, the relationship between matrix multiplication and a problem like this isn't obvious.

As far as extending the formula, I was afraid you'd want that! ? It's not hard, but it obscures what I thought was a pretty elegant formula into something messier:

Book1
	A	B	C	D	E	F	G	H	I
1		Number of Weekly Wins	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7
2	Player 1	2	10	7	8	9	6	1
3	Player 2	2	8	10	8	6	5	2
4	Player 3	1	4	8	7	10	6	3
5	Player 4	1	5	6	8	7	8	4
6	Player 5	3	7	8	8	4	9	5
7
Sheet5

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(SUMPRODUCT(--(MMULT(COLUMN(INDIRECT("A1:"&ADDRESS(1,COUNTA($A$2:$A$20))))^0,--(OFFSET(C2,0,0,1,COUNT(C2:AX2))>=OFFSET($C$2,0,0,COUNTA($A$2:$A$20),COUNT(C2:AX2))))=COUNTA($A$2:$A$20))),"")

Just put the maximum size ranges into the formula, and drag it down as far as you want, 15 rows if that is the maximum number of players. Then the formula will automatically adapt if you add new players or new weeks to your table.

 

[Eric W]

Here's another one - it's a fair amount shorter, also expands in both directions, but will give erroneous results if the entire grid isn't filled out (delete F7 for example to see what happens).

Book1
	A	B	C	D	E	F	G	H	I
1		Number of weekly wins	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7
2	Player 1	2	10	7	8	9	6
3	Player 2	2	8	10	8	6	5
4	Player 3	1	4	8	7	10	6
5	Player 4	1	5	6	8	7	8
6	Player 5	2	7	8	8	4	9
7	Player 6	1	4	0	5	10	1
8
Sheet5

Cell Formulas
Range	Formula
B2:B8	B2	=IF(A2="","",SUMPRODUCT(--(COUNTIF(OFFSET(C:C,0,COLUMN(C2:AZ2)-COLUMN(C2)),"<="&C2:AZ2)=COUNTA($A$2:$A$20))))

 

[RAyerst]

Eric, thank you so much, massively helpful