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

### Category: Count values

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]Comments(93) Filed in category: Count unique distinct values, Count values, Excel

Count unique distinct values in a column

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: First, let me explain […]Comments(26) Filed in category: Count unique distinct values, Count values, Excel

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]Comments(26) Filed in category: Conditional formatting, Count values, Excel

### Category: Mmult function

Comments(13) Filed in category: Excel, MMULT function, Permutations, SUM function

Filter rows where a cell contains a numeric value

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]Comments(10) Filed in category: Excel, MMULT function

### Category: Sum function

Identify numbers in sum using solver

Here is a useful feature I recently found googling for Excel solver examples. I have summed some random values from […]Comments(48) Filed in category: Combinations, Excel, Solver, SUM function

Sum values between two dates with criteria in excel

In this post, I will provide a formula to sum values in column (Qty) where an column (Date) meets two […]Comments(27) Filed in category: Excel, SUM function

Sum values in a range where adjacent cell value equals a criterion in excel

Question: How do I sum all values in a range where adjacent cell value equals a criterion? The criterion is […]Comments(19) Filed in category: Excel, SUM function

### 7 Responses to “Dynamic scoreboard”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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?