How to Use COMBIN Function in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


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)

Excel COMBIN Function

  • Argument
Argument Required/Optional Value
number Required The number of items. It should be greater than or equal to zero as well as 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 for this parameter also.
  • 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.

Dataset for Excel COMBIN Function

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)

Excel COMBIN Function

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

Excel COMBIN Function


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

Excel COMBIN Function

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

Excel COMBIN Function

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

Read More: How to Create All Combinations of 6 Columns in Excel


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.

Read More: How to Generate All Possible Combinations of a Set of Numbers in Excel


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

Excel COMBIN Function in VBA Code

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.

Read More: How to Find Combinations Without Repetition in Excel


💡 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!!!


Excel COMBIN Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo