Today, we are going to learn about the Excel **COMBIN **function. This tutorial will provide a complete overview of the **COMBIN **function in detail, the objective of this function, and some relatable real-life examples about the uses of the **COMBIN **function in Excel.

So. let’s start!

## Excel COMBIN Function Overview

**Combination**

In mathematical calculations, *Combination* is defined as a technique of selecting items from a provided list of items without considering the order of selection. Don’t confuse *Combination* with *Permutation!* The main difference between *Combination* and *Permutation* is that *Permutation* takes the sequence of order into account, whereas *Combination* ignores the order.

**COMBIN **function is a Math and Trigonometry type function for performing mathematical calculations in Excel. This function determines the possible number of combinations from a given data range.

**Objective**

**COMBIN** function is used to determine the total possible number of combinations for a given number of items without any repetition in Excel.

**Syntax**

*=COMBIN(number, number_chosen)*

**Argument**

Argument |
Required/Optional |
Value |
---|---|---|

number |
Required | The number of items. It should be greater than or equal to as well as zero 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 . Non-integer values are truncated for this parameter also.zero |

**Output**

Returns the number of combinations for a given number of items.

**Versions**

**COMBIN** function was first introduced in MS Excel in version 2000. So it is workable from this version and later on. I have used *MS 365* for this article. You may proceed with your functional version.

Let’s say five friends named Mike, Adam, Stuart, David, and Parker want to play games by forming a team.

The total number of players here is 5. The players can form teams with different numbers (i.e. starting from 1 to 5) of members in a group. We want to find out how they can form teams with 2 players in a group.

So. let’s check how we can perform this task. In this section, we will see the use of the **COMBIN **function for finding the possible number of combinations of players. I will demonstrate them here with proper illustrations. Let’s check them now!

## 1. Calculating the Number of Combinations Directly with the COMBIN Function

In the concerning dataset, we have seen that there are 5 players in total. Our aim is to form some teams with 2 members in a group. So we need to find out the possible number of combinations of 2 players out of 5. For this, we will apply the **COMBIN **function. You can just directly input the parameters into the arguments of the function. Let’s proceed with the following steps.

⏩ **Steps**:

- First of all, select a cell where you want to find the possible number of combinations from your dataset.
- Then, type the following formula in that cell:

`=COMBIN(5,2)`

Here,

**5**= Number (i.e. total number of players)**2**= Number Chosen (i.e. number of players in a group)

- Then, press
**ENTER**. The cell will auto-calculate the number of combinations from the input parameters of the**COMBIN**function. You can see that the cell has shown 10 as output. That means there are 10 possible groups having 2 members in each of the groups.

Just think with real-life examples, and you will find the 10 possible groups. We have shown the 10 duos with the 5 members of our dataset.

## 2. Using Excel COMBIN Function with Cell Reference

In the previous dataset, we used the value in the argument directly. We can also use the cell reference in the argument of the function. In our previous dataset, the number of players was 5 in total. Now, we will calculate the possible teams starting from 1 to 5 in each group.

Follow the steps below for this purpose.

⏩ **Steps**:

- Firstly, select a cell for finding the number of possible teams with 1 member in each group.
- Then, type the following formula in the cell.

`=COMBIN($E$7,B5)`

Here,

**$E$7**= Number (i.e. total number of players)**B5**= Number Chosen

- Then, press
**ENTER**and you will see that 5 teams are possible with 1 member in each group. This definitely makes sense, and you don’t even need to overthink that.

- Now, use the Fill Handle tool to copy the formula for finding the other groups with members 2, 3, 4, and 5 in each of them. Just drag the tool downward or double-click it.

*Note**: As each time, we need to find the teams out of 5 players (which is fixed), so we have used the Absolute Cell Reference for the cell with total players.*

- Hence, you will get all the possible combinations with corresponding chosen numbers.

## 3. Nesting COUNTA Function with COMBIN Function

Sometimes, you may not have the numbers directly that you need to input as the argument; rather, you have a clue, and you need to find out the numbers first.

In that case, using the **COUNTA function **can be a fruitful way to serve your purpose. The **COUNTA** function counts the total number of non-empty cells in a given range. So, using the **COUNTA **function nested in the **COMBIN **function will act as an argument for the **COMBIN **function.

⏩ **Steps**:

- First, type the following formula to find the number of combinations of 2 members in each group.

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

Here, the **COUNTA** function counts the total number of cells within the range **(C5:C9)**. So, it counts the total number of players in that range. We want to create teams with 2 players and so we used the second argument 2.

- Next, press
**ENTER**and you will get your desired output.

## 4. Using the COMBIN Function in VBA Code

You can also use the **COMBIN **function in a **VBA **code to find the same output for the concerned dataset.

⏩ **Steps**:

- First of all, press
**ALT+F11**to activate the Visual Basic Editor window. - Now,
**Insert**a new**Module**and apply the following code in the**Module**window.

**Code**:

```
Sub use_COMBIN_function()
Dim rfCombin As Double
rfCombin = Application.WorksheetFunction.Combin(5, 2)
MsgBox (rfCombin)
End Sub
```

Here, we have applied code for creating groups of 2 items out of 5.

- Then, press
**ALT+Q**to switch back to the worksheet. - Next, press
**ALT+F8**to open the**Macro**dialog box-> select the**Macro Name**and click**Run**.

- The output will be shown in a message box finally.

## 💡 Quick Notes

- The function returns
**#VALUE!**error when you apply non-numerical value as an argument. - The formulated cell will show
**#NUM!**error when the first argument is less than**0**or**Negative**and the second argument is negative or greater than the first argument. **COMBIN**function ignores repetition. If the sequence of the order needs to be considered, then you need to use the**PERMUT function**instead of the**COMBIN**function, as the order is significant for the**PERMUT**function.

**📁 Download Practice Workbook**

You can download the practice book from the link below.

## Conclusion

In this tutorial, I have tried to show you some ways of using the **COMBIN **function in Excel. Hope you like reading the article and, from now on, you can apply this function to serve your purpose. Don’t forget to leave a comment if you have any queries or feedback regarding this article. Happy Excelling!!!