COMBINA Function

It returns the number of different possible combinations for a given number of items, allowing repetition.

Author: Emily Rustom
Emily Rustom
Emily Rustom
I'm a BBA Finance and Economics student at Texas A&M university from Houston, Texas. On campus, I’m involved in Delta Gamma Sorority, Aggies on Wall Street, Aggie Women in Business, Horizons Finance, and Aggie Investment club on top of my job as a Fashion Marketing Coordinator. Outside of university, I've had experiences participating in the PJT Partners cohort program, WSO internship, UTIMCO Scholars program, and a Financial Officer of Grace in His hands NonProfit. These programs allowed me to develop skills in Excel, time management, organization, PowerPoint, and enhance my industry knowledge. Reach out on my Linkedin or email for more information!
View Profile
Reviewed By: Osman Ahmed
Osman Ahmed
Osman Ahmed
Investment Banking | Private Equity

Osman started his career as an investment banking analyst at Thomas Weisel Partners where he spent just over two years before moving into a growth equity investing role at Scale Venture Partners, focused on technology. He's currently a VP at KCK Group, the private equity arm of a middle eastern family office. Osman has a generalist industry focus on lower middle market growth equity and buyout transactions.

Osman holds a Bachelor of Science in Computer Science from the University of Southern California and a Master of Business Administration with concentrations in Finance, Entrepreneurship, and Economics from the University of Chicago Booth School of Business.

View Profile
Last Updated:December 4, 2023
In This Article

What is the COMBINA Function?

The Combina Function returns the number of different possible combinations for a given number of items, allowing repetition. The function is built into Microsoft Excel and falls under the Excel Math and Trigonometry functions.

This particular feature was recently added to Microsoft Excel in 2013 and helps determine groups, objects, product placements, and more.

The function is categorized under worksheet function (WS) and is utilized by being inserted as a formula inside a cell. It is available in Excel for Office 365, Excel 2019, Excel 2016, and Excel 2013.

Multiple functions in Excel give similar returns to the COMBINA(), such as:

  1. COMBIN: Returns the number of combinations of two numbers, excluding repetition.
  2. PERMUT: Permut counts the number of Permutations between two numbers. A permutation is a combination where the order does matter.

NOTE

The difference between the Combin() and Combina() functions is that Combina() allows for repetition.

Key Takeaways

  • Combina returns a repeated combination of numbers in no particular order.
  • The Number parameter must be a positive integer.
  • The Number Chosen integer must be a positive integer less than the Number.
  • The most common errors are #NUM and #VALUE!
  • The function can be applied daily to real-world examples.
  • This function is best used in a list format.
  • Do not use this function when combinations are in a specific order. 

How is the Combina Function used?

To use the Combina Function, it's essential to understand the purpose of the function.

The general purpose is to find how many combinations of a given number you can get from another larger specified number (including repetitions).

Let's break this down piece by piece:

1. COMBINA() 
The required argument is the worksheet function that must be entered into your cell to begin the process of calculating your return.

2. Number 

The required argument is the larger number of the argument that must be a positive integer. It must also be greater than or equal to the (number_chosen) argument. This serves as the number of items.

3. Number_chosen

Required argument. The number_chosen part of the argument must be equal to or less than the number. This is the number of items that make up how many combinations are possible.

If a non-integer value is inputted like a decimal, Excel will condense it into an integer. Combina Function will always return a numeric value. 

Examples of the Combina Function

Example 1: In this first example, let’s pick sets of two numbers that fit the constraints of the function. Remember the rules from earlier our first (number,_) needs to be greater than zero, and our (_,number_chosen) for the formula to compute correctly.

1. The Number column on the left represents our (number) in this list. 

  • 29
  • 10
  • 6
  • 8

2. Our (number_chosen) in this scenario is the number of groups represented by the second column from the left, “Chosen Number.”

  • 3
  • 5
  • 2
  • 2

3. Our Result is what the function returns, which means how many possible repeated combinations the two numbers have.

  • List one has a possibility of 4495 different groups.
  • List two has a possibility of 2,002 different groups.
  • List three has a possibility of 21 different groups.
  • List four has a possibility of 36 different groups.

4. We implemented each number in the correct arguments for our formula to find our outcome. 

Example 2: Now that we understand the basic computation, let's apply this to a real-world scenario. In this scenario, you and 2 friends are playing video games. In this game, there are multiple characters each of you can choose from, and the characters can be repeated.

Options:

  1. Video game 1 is represented by the third row.
  2. Video game 2 is represented by the fourth row.
  3. Video game 3 is represented by the fifth row.
  4. Video game 4 is represented by the sixth row.

1. The number of selections of characters for each video game is represented by the (Number) column in this scenario.

  • Video game one has 15 character selections
  • Video game one has 100 character selections
  • Video game one has 60 character selections
  • Video game one has 20 character selections

2. The number of players for each video game is represented by the (Number) column in this scenario.

  • 3

3. Our result is what our function returns, which is all the player combinations that are possible when playing each video game.

  • 680 combinations of characters between the three players
  • 171700 combinations of characters between the three players
  • 37820 combinations of characters between the three players
  • 1540 combinations of characters between the three players

4. We implemented each number in the correct arguments for our formula to find our outcome.

Example 3: We’ll use one more simple example to ensure the concept is understood. Still keeping in mind our rules and constraints for the numeric value.

1. The Number column on the left represents our (number) in this list. 

  • 30
  • 23
  • 10
  • 50

2. Our (number_chosen) in this scenario is represented by the second column from the left, “Chosen Number.”

  • 2
  • 3
  • 6
  • 4

3. Our Result is what the function returns, which means how many possible repeated combinations the two numbers have

  • List one has a possibility of 465 different groups.
  • List two has a possibility of 2,300 different groups.
  • List three has a possibility of 5,005 different groups.
  • List four has a possibility of 29,2825 different groups.

4. We implemented each number in the correct arguments for our formula to find our outcome. 

The Math Behind the COMBINA Function

Even though excel calculates your combinations for you, with this function, it might be helpful to understand the math behind it to increase comprehension.

  • N = number
  • M = number_chosen

(N + M - 1) / (N - 1) 

Results can be calculated mathematically easily by plugging them into the formula. This works by subtracting the numerator by one to allow for repetitions. 

This works because when entering two numbers in the COMBINA syntax, you ask Excel to calculate how many number_chosen combinations can be created from the number provided.

In the example above, labeled Figure 1, we can see how this formula plays out when looking at how the numbers can repeat and how each combination is counted once.

This same result can be found by plugging two numbers into the formula. If our formula was COMBINA(0,) then we would solve it as:

0 + 0 - 1 = -1/ (0 - 1) = -1 = -1/ -1 = 1

Common Errors Using COMBINA Function

When using Excel functions for the first time, it's very common to get repeated errors. These errors are easy to make and cause frustration. Excel uses errors to communicate a restriction in the function. This is helpful in computing equations.

To avoid this, here are some of the most common errors when using the COMBINA Function:

1. #VALUE! Error

  • This error occurs when entering a non-numerical value when a function only accepts numeric values. For example, if a non-numeric value is entered in the (number,_) or (_, number_chosen) parameter, it will produce the #VALUE! Error.
  • The #VALUE! error can occur with any function that requires the use of numbers only.#VALUE! Error

2. #NUM Error

  • This error occurs when a number is out of the constraint. If you remember, COMBINA has specific rules for each number used in the argument. This error will occur if
    • (Number,_) < 0 
    • (_, number_chosen) < 0 OR (_, number_chosen) > (Number,_)
  • Users typically make this error when clicking on the wrong cell. 

#NUM Error

NOTE

The #NUM error can be made with any function with inequality restrictions (<,>, or =). Therefore, it’s essential to know argument restrictions to avoid this mistake.

Excel Modeling Course

Everything You Need To Master Excel Modeling

Banner that leads users to the Excel Modeling Course landing page.

Learn More

Researched and authored by Emily Rustom | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources: