COMBINA Function Examples – Excel, VBA, & Google Sheets

This tutorial demonstrates how to use the Excel 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:

(Notice how the formula inputs appear)

 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

Summer has just approaching and you’re itching for an ice cream. You walk into your favourite ice cream store but they only have 4 flavours 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 flavours? Note for this example, the order of the flavours 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 favourite ice cream store and they have four flavours left, COMBINA will tell you how many ways there are to stack your triple scoop even if you want the flavour 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)

For the function arguments (number, etc.), you can either enter them directly into the function, or define variables.