Count Number of Instances of MAX across multiple columns

RAyerst

New Member
Joined
Jun 1, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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:

Capture.JPG


Any help gratefully received, thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,666
Welcome to the MrExcel message board!

Try:

Book1
ABCDEFG
1Number of Weekly WinsWeek 1Week 2Week 3Week 4Week 5
2Player 12107896
3Player 22810865
4Player 31487106
5Player 4256878
Sheet5
Cell Formulas
RangeFormula
B2:B5B2=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

New Member
Joined
Jun 1, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,666
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
ABCDEFGHI
1Number of Weekly WinsWeek 1Week 2Week 3Week 4Week 5Week 6Week 7
2Player 121078961
3Player 228108652
4Player 314871063
5Player 41568784
6Player 53788495
7 
Sheet5
Cell Formulas
RangeFormula
B2:B7B2=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.
 
Solution

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,666
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
ABCDEFGHI
1Number of weekly winsWeek 1Week 2Week 3Week 4Week 5Week 6Week 7
2Player 12107896
3Player 22810865
4Player 31487106
5Player 4156878
6Player 5278849
7Player 61405101
8 
Sheet5
Cell Formulas
RangeFormula
B2:B8B2=IF(A2="","",SUMPRODUCT(--(COUNTIF(OFFSET(C:C,0,COLUMN(C2:AZ2)-COLUMN(C2)),"<="&C2:AZ2)=COUNTA($A$2:$A$20))))
 

RAyerst

New Member
Joined
Jun 1, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Eric, thank you so much, massively helpful
 

Forum statistics

Threads
1,147,743
Messages
5,742,936
Members
423,765
Latest member
PaulD1984

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top