# How to Use the COMBIN Function in Excel – 4 Examples

This is an overview

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

## Excel COMBIN Function: Knowledge Hub

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF