How to Use the COMBIN Function in Excel - 4 Examples

This is an overview

The Excel COMBIN Function

Objective

determines the total possible number of combinations in a given number of items.

Syntax

=COMBIN(number, number_chosen)

Arguments

Argument	Required/Optional	Value
number	Required	The number of items. It should be greater than or equal to *zero* and to *number_chosen*. Non-integer values are truncated if entered into this parameter.
number_chosen	Required	The number of items in each combination. It must be greater than or equal to *zero*. Non-integer values are truncated.

Output

the number of combinations in a number of items.

This is the sample dataset.

Example 1 – Calculating the Number of Combinations with the COMBIN Function

Steps:

Select a cell to see the possible number of combinations.
Enter the following formula:

=COMBIN(5,2)

5= Number (total number of players)
2= Number Chosen (number of players in a group)

Press ENTER.

10 is the output. There are 10 possible groups with 2 members.

These are the combinations:

Example 2 – Using the Excel COMBIN Function with a Cell Reference

Calculate the possible teams with 1 to 5 players:

Steps:

Select a cell to see the number of possible teams with 1 member in each group.
Enter the following formula:

=COMBIN($E$7,B5)

$E$7= Number (total number of players)
B5= Number Chosen

Press ENTER.

5 teams are possible with 1 member.

Drag down the Fill Handle to copy the formula and find the other groups with 2, 3, 4, and 5 members:

Note:The Absolute Cell Reference is used in the cell with the total number of players.

This is the output.

Example 3 – Combining the COUNTA Function with the COMBIN Function

Steps:

Enter the following formula to find the number of combinations of 2 members in each group.

=COMBIN(COUNTA(C5:C9),2)

The COUNTA function counts the total number of cells in C5:C9 (the total number of players). To create teams with 2 players, 2 is used in the second argument.

Press ENTER to see the output.

Example 4 – Using the COMBIN Function in a VBA Code

Steps:

Press ALT+F11 to open the Visual Basic Editor window.
Insert a new Module and enter the following code:

Sub use_COMBIN_function()

Dim rfCombin As Double

rfCombin = Application.WorksheetFunction.Combin(5, 2)
MsgBox (rfCombin)

End Sub

It will create groups of 2.

Press ALT+Q to go back to the worksheet.
Press ALT+F8 to open the Macro dialog box-> select the Macro Name and click Run.

This is the output.

Quick Notes

The function returns the #VALUE! error when a non-numerical value is used as argument.
It displays the #NUM! error when the first argument is less than 0 or Negative and the second argument is negative or greater than the first argument.
The COMBIN function ignores repetition. To consider the sequence, use the PERMUT function.

Download Practice Workbook

Download the practice workbook.

Use of COMBIN Function.xlsm