COMBINA Function Examples – Excel, VBA, & Google Sheets
Written by
Reviewed by
This tutorial demonstrates how to use the COMBINA Function in Excel to calculate the number of ways a given number of items can be ordered, taking into account repetitions.
COMBINA Function Overview
The COMBINA function calculates the number of combinations with repetitions for a given number of items.
To use the COMBINA Excel Worksheet Function, select a cell and type:
COMBINA Function Syntax and Inputs:
=COMBINA(number,number_chosen)number – The number of items you can choose from.
number_chosen – The number of items in each combination
COMBINA Example
Summer has just approaching and you’re itching for an ice cream. You walk into your favorite ice cream store but they only have 4 flavors left in their freezer – Chocolate, Strawberry, Banana, and Mint.
How many ways could you order a triple scoop, taking into account that all could be Mint or you could have three different flavors? Note for this example, the order of the flavors doesn’t matter. Chocolate-Mint-Strawberry is the same as Mint-Strawberry-Chocolate.
You step aside while you calculate this, remembering that Excel can calculate it quickly using the COMBINA function.
=COMBINA(C3,D3)
You calculate there to be 20 ways of ordering a triple scoop. You tell this to the owner and he gives you the ice cream for free because this information will surely bring in extra math-loving customers!
We can check this by listing out all the possible combinations (C = Chocolate, B = Banana, S = Strawberry, M = Mint). Each row on the right is a different combination of flavors.
Common Errors
#NUM! Combinations work in the natural world with natural numbers (including zero), so the argument of the COMBINA function is negative, a #NUM! error will be returned.
#NUM! In order to choose a number of items from a list of items, that number of chosen items must be equal to or less than the number of items.
Additional Notes
If you enter either the number or number_chosen as a non-integer, the decimal portion of the numbers will be ignored. Only integer values will be calculated.
Unlike the COMBIN function, the COMBINA formula does take into account repetition. So if you go to your favorite ice cream store and they have four flavors left, COMBINA will tell you how many ways there are to stack your triple scoop even if you want the flavor repeated!
From combinatorics, the COMBINA formula is equivalent to the following function, where n is the number and r is the number_chosen:
COMBINA in Google Sheets
The COMBINA Function works exactly the same in Google Sheets as in Excel:
COMBINA Examples in VBA
To find the number of combinations a given number number of items with repetition, use:
Dim CombWithRep As Double
CombWithRep = WorksheetFunction.Combina(Arg1, Arg2)
