Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each time you enter or edit scores.
This entire scoreboard is based on formulas only and it is the amazing MMULT function that makes it possible.
The Excel defined Table, shown to the right in the picture above, allows you to enter as many names as you want, no need to update cell references in formulas.
Array formula in cell C1:
The formula works even if two or more people share the same total score.
How to enter an array formula
An array formula is a regular formula on steroids, it allows you to perform much more complicated calculations with amazing results.
To enter an array formula you must follow these steps:
 Copy array formula above (CTRL + c).
 Double click on cell C1.
 Paste array formula to the cell.
 Press and hold CTRL + SHIFT simultaneously.
 Press Enter once.
 Release all keys.
If you did it right the formula in the formula bar now begins and ends with curly brackets, like this ={array_formula}. Don't type these characters yourself, they appear if the above steps were correctly done.
Note, if a user edits an array formula and then press Enter key on the keyboard the array formula becomes a regular formula, however, if a user press Esc key on the keyboard the formula returns to an array formula.
This is the single most annoying thing with array formulas, users can easily break them.
How to create an Excel Table
The Excel Table has many useful features, in this case, one specific feature is really great. It allows you to add or delete names to the scoreboard without adjusting cell references, you can have as many names as you like in the Excel Table.
 Select any cell in your data set.

Press shortcut keys CTRL + T to display the Excel Table dialog box.
 Click OK button to apply settings and create the table.
Hiding zeros
I have applied custom cell formatting to cell range D3:K7 in order to hide 0's (zeros), you can skip these steps if you want them in your worksheet.
 Select cell range D3:K7
 Press short cut keys CTRL + 1 to open the "Format Cells" dialog box.
 Go to tab "Number".
 Click "Custom" category.
 Type: 0;0;;@
 Click OK button to apply cell formatting.
Explaining formula in cell C3
Step 1  Check if scores are empty
This step creates an array based on cell range D3:K7 and replaces blank values with 0's (zeros).
The less than and greater than characters combined means not equal to and in this case we compare nothing "" meaning an empty cell.
Table2[['#1]:['#8]] is a structured reference pointing to columns #1 to #8 in Table2, Table2 is the name of the Excel Table.
Table2[['#1]:['#8]]<>""
becomes
{10, "", "", "", "", "", "", "";8, "", "", "", "", "", "", "";6, "", "", "", "", "", "", "";4, "", "", "", "", "", "", "";2, "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", ""}<>""
and returns
{TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}
The IF function can now, based on the above array, return a specific value if TRUE and another value if FALSE.
IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0)
becomes
IF({TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}, {10, "", "", "", "", "", "", "";8, "", "", "", "", "", "", "";6, "", "", "", "", "", "", "";4, "", "", "", "", "", "", "";2, "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "";"", "", "", "", "", "", "", ""}, 0)
and returns
{4, 0, 0, 0, 0, 0, 0, 0;2, 0, 0, 0, 0, 0, 0, 0;8, 0, 0, 0, 0, 0, 0, 0;6, 0, 0, 0, 0, 0, 0, 0;10, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0}
Step 2  Create column numbers based on Excel Table headers
The COLUMN function returns a column number based on a cell reference.
COLUMN(Table2[[#Headers], ['#1]:['#8]])
becomes
COLUMN(O2:V2)
and returns {15, 16, 17, 18, 19, 20, 21, 22} which means that the headers are located in column number 15 to 22.
Step 3  Transpose numbers
The array size is correct, however, all numbers must be 1 to be able to use them in the MMULT function in order to return the desired result. To achieve that we can calculate powers of numbers, in this case power of 0 (zero) which will return 1.
TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)
becomes
TRANSPOSE({15, 16, 17, 18, 19, 20, 21, 22}^0)
becomes
TRANSPOSE({1, 1, 1, 1, 1, 1, 1, 1})
To be able to use the numbers with the MMULT function they need to be separated by semicolons which is easy to accomplish, the TRANSPOSE function changes the array from being arranged horizontally to vertically.
TRANSPOSE({1, 1, 1, 1, 1, 1, 1, 1})
returns
{1; 1; 1; 1; 1; 1; 1; 1}
Step 4  Add numbers columnwise
The MMULT function allows us to sum the numbers horizontally using two arrays.
MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0))
becomes
MMULT({4, 0, 0, 0, 0, 0, 0, 0;2, 0, 0, 0, 0, 0, 0, 0;8, 0, 0, 0, 0, 0, 0, 0;6, 0, 0, 0, 0, 0, 0, 0;10, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0}, TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0))
becomes
MMULT({4, 0, 0, 0, 0, 0, 0, 0;2, 0, 0, 0, 0, 0, 0, 0;8, 0, 0, 0, 0, 0, 0, 0;6, 0, 0, 0, 0, 0, 0, 0;10, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0, 0}, {1; 1; 1; 1; 1; 1; 1; 1})
and returns {4; 2; 8; 6; 10; 0; 0; 0; 0; 0; 0}. The numbers in the array are in the same order as the names in the Excel Table which is neccessary to be able to sort the names based on scores.
Step 5  Extract the kth largest score
The LARGE function calculates the kth largest number in the array. LARGE(array, k)
LARGE(MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), ROWS($A$1:A1))
becomes
LARGE({4; 2; 8; 6; 10; 0; 0; 0; 0; 0; 0}, ROWS($A$1:A1))
The ROWS function contains a cell reference that is both absolute and relative, this makes the cell reference grow when the formula is copied to cells below. This is needed in order to extract a new value in each cell.
LARGE({4; 2; 8; 6; 10; 0; 0; 0; 0; 0; 0}, ROWS($A$1:A1))
becomes
LARGE({4; 2; 8; 6; 10; 0; 0; 0; 0; 0; 0}, 1)
and returns 10.
Step 6  Find relative position in array
We have now calculated the kth largest number in the array and this step explains how to find the position of that value in the array. The MATCH function allows us to do that.
MATCH(LARGE(MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), ROWS($A$1:A1)), IF((COUNTIF($C$2:C2, Table2[Name])=0), MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), "A"), 0)
becomes
MATCH(10, IF((COUNTIF($C$2:C2, Table2[Name])=0), MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), "A"), 0)
becomes
MATCH(10, {4; 2; 8; 6; 10; 0; 0; 0; 0; 0; 0}, 0)
and returns 5. Number 10 is found in position 5 in the array.
Step 7  Extract the corresponding record to the calculated position
The INDEX function lets you fetch a value based on row and column number.
INDEX(Table2, MATCH(LARGE(MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), ROWS($A$1:A1)), IF((COUNTIF($C$2:C2, Table2[Name])=0), MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), "A"), 0), COLUMNS($A$1:A1))
becomes
INDEX(Table2, 10, COLUMNS($A$1:A1))
The COLUMNS function chnages when the formula is copied to cells located to the right, this lets us extract the entire record.
INDEX(Table2, 10, COLUMNS($A$1:A1))
becomes
INDEX(Table2, 10, 1)
and returns "Katy" in cell C3.
Step 8  Remove errors
The IFERROR function removes errors that will show up when all names have been displayed.
IFERROR(INDEX(Table2, MATCH(LARGE(MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), ROWS($A$1:A1)), IF((COUNTIF($C$2:C2, Table2[Name])=0), MMULT(IF(Table2[['#1]:['#8]]<>"", Table2[['#1]:['#8]], 0), TRANSPOSE(COLUMN(Table2[[#Headers], ['#1]:['#8]])^0)), "A"), 0), COLUMNS($A$1:A1)), "")
Interested in learning more about array formulas? Join my Advanced Excel Course.
Animated image
The image above shows the scoreboard while I am updating the scores, it sorts the names based on the total.
Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to [โฆ]
Extract records containing digits [Formula]
Liam asks: Hello Oscar, What code is needed to cause cells in Columns F  I to fill with the [โฆ]
Count overlapping days in multiple date ranges
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges [โฆ]
The MMULT function calculates the matrix product of two arrays, an array as the same number of rows as array1 and [โฆ]
Count overlapping days across multiple date ranges
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are [โฆ]
Working with overlapping date ranges
Today's blog post is about date ranges, the techniques demonstrated here can also be applied to time or other numerical [โฆ]
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in [โฆ]
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. [โฆ]
Get date ranges from a schedule
The above picture shows you two formulas that extract names (column B) and date ranges (column C and D) based [โฆ]
11 Responses to โDynamic scoreboardโ
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form
Oscar 
Great concept  I'll be using this for sure. One question:
You probably had a reason to have the COUNTIF statement and the final COLUMNS. But, by entering the entire nontotal row as a single array value you can use the ability of INDEX to return an entire row or column using 0 as the parameter.
=INDEX($C$11:$K$15, MATCH(LARGE(MMULT(IF($D$11:$K$15<>"", $D$11:$K$15,0), ROW(INDIRECT("1:8"))), ROWS($C$11:$C11)), MMULT(IF($D$11:$K$15<>"", $D$11:$K$15, 0), ROW(INDIRECT("1:8"))), 0), 0)
GMF
Thank you, yes the COUNTIF function keeps track of names with the same total score.
Sorry, wordpress eats html characters and I donยดt know how to fix it. I edited your formula.
Sorry, the "not equal" values didn't get through the URL encoding. Let's try
OK, I get the COUNTIF saves duplicates! One last time for the INDEX construction i talked about...
I'll bet there's a cleaner way to generate an array of 1's for the MMULT function to cope with flexible numbers of columns for calculating the total, but I couldn't think of it right away!
Here is what I did to create the one's vector
=INDEX($C$11:$K$15,MATCH(LARGE(MMULT(IF($D$11:$K$15"",$D$11:$K$15,0),(1+0*ROW(OFFSET(INDIRECT("A1"),0,0,COUNTA($D$10:$K$10),1)))),ROWS($A$3:B3)),(COUNTIF($C$2:D2,$C$11:$C$15)=0)*MMULT(IF($D$11:$K$15"",$D$11:$K$15,0),(1+0*ROW(OFFSET(INDIRECT("A1"),0,0,COUNTA($D$10:$K$10),1)))),0),COLUMNS($A$3:B3))
Can the above formula be used for 100 tables for 10 rounds? What would the formula to carry this out?
Instead of just 5 how would I do this for 50 people?
I need help developing onto your dynamic scoreboard. I would like it to be 50 down and 50 across. Please help.
Hi Oscar very clever how would I alter it to manage the score of 16 people?
Charles Clarke
Array formula in cell C3:
=INDEX($C$21:$K$36, MATCH(LARGE(MMULT(IF($D$21:$K$36<>"", $D$21:$K$36, 0), {1; 1; 1; 1; 1; 1; 1; 1}), ROWS($A$1:A1)), (COUNTIF($C$2:C2, $C$21:$C$36)=0)*MMULT(IF($D$21:$K$36<>"", $D$21:$K$36, 0), {1; 1; 1; 1; 1; 1; 1; 1}), 0), COLUMNS($A$1:A1))
Dynamicscoreboard_Charles.xlsx
Charles Clarke,
I have now changed this article so any number of people can be managed.