## Dynamic scoreboard

Here comes another post about the MMULT function, today I made a dynamic scoreboard.

There are five women competing and there are 8 rounds. Look what happens with the scoreboard as I type new values in round 3.

**Here is the array formula in cell C1:**

The formula works even if two or more people share the same total score.

Want to know more about the functions in the array formula above? INDEX, MATCH, COUNTIF, ROW, MMULT and LARGE

### How to enter an array formula

- Copy array formula above (CTRL + c)
- Select cell C1
- Click in formula bar
- Paste array formula to formula bar
- Press and hold CTRL + SHIFT simultaneoulsy
- Press Enter once
- Release all keys

If you did it right the formula in the formula bar now begins and ends with a curly brackets, like this ={array_formula}. Don´t type these characters yourself.

### Hiding zeros

I have hidden zeros in cell range D3:K7 by applying cell formatting, these are the steps:

- Select cell range D3:K7
- Press CTRL + 1
- Go to tab "Number"
- Click "Custom"
- Type: 0;-0;;@
- Click OK

Interested in learning more about array formulas? Join my Advanced excel course.

### Download excel *.xlsx file

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

NC asks: Thanks a tonne, Oscar. It took me about 8 hours to work through this formula piece by piece, […]

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 […]

### 8 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 non-total 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.