# Count Number of Instances of MAX across multiple columns

#### RAyerst

##### New Member
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:

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Eric W

##### MrExcel MVP
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
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
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

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

##### MrExcel MVP
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
Eric, thank you so much, massively helpful

Replies
4
Views
449
Replies
1
Views
166
Replies
3
Views
434
Replies
1
Views
258
Replies
8
Views
215

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,588
Messages
5,765,303
Members
425,271
Latest member
kristyfinn

### 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.

### Which adblocker are you using?

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

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