Author: Oscar Cronquist Article last updated on August 25, 2017

This blog article describes how to create teams randomly. There are twenty names in column B and four teams in column E. There will be five team members in each team. In column C a team is randomly selected in each cell.


Array formula in cell C2:

=INDEX($E$2:$E$5, LARGE((COUNTIF($C$1:C1, $E$2:$E$5)<5)*(ROW($1:$4)), RANDBETWEEN(1, SUMPRODUCT(--(COUNTIF($C$1:C1, $E$2:$E$5)<5)))))

This article demonstrates how to generate teams with different sizes:

Team generator

JD asks in this post: Dynamic team generator Hi, what if we have different number of people per team? So in […]

The following shows you how to setup a dynamic scoreboard, enter results and the scoreboard is automatically recalculated:

Dynamic scoreboard

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

How to create an array formula

  1. Copy the aray formula above (Ctrl + c)
  2. Double click cell B2
  3. Paste (Ctrl + v)
  4. Press and hold Ctrl + Shift simultaneously
  5. Press Enter
  6. Release all keys

If you made the above steps correctly the formula now has a beginning and ending curly bracket, like this:
{=array_formula}

Don't enter these characters yourself, they appear automatically.

How to enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

How to copy formula

Copy cell C2 and paste it down.

Customize formula

COUNTIF($C$1:C1, $E$2:$E$5)<5 makes sure that each team has max five team members.

ROW($1:$4) gives a row number to each team. Example, if you have three teams, change it to ROW($1:$3) otherwise the formula won´t work.

Download excel sample file for this tutorial

Team Generator.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

How to use the SUMPRODUCT function

The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the SMALL function

The SMALL function lets you extract a number in a cell range based on how small it is compared to the other numbers in the group.

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to generate random numbers and text

What's on this page Random numbers with a decimal Random negative numbers with two decimals Random negative numbers with three […]