Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Calculating Combinations.
Have you ever wondered how many different four-character combinations you can make when you start with 26 letters and 10 digits? Excel can tell you in a flash, if you use the COMBIN worksheet function. The syntax of the function is as follows:
=COMBIN(universe, sets)
In this syntax, universe represents the number of items from which the combinations can be made, and sets represents the number that must be in each combination. Thus, you could use the following to determine the number of four-character combinations that you can make from the alphabet (26 letters) and the ten digits:
=COMBIN(26+10,4)
Just for those who are curious, the answer returned is 58,905.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12202) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Calculating Combinations.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Ever want the IF function to only return a value if the condition it is testing is true, and not if the condition is ...
Discover MoreIn the newest version of Excel, a change in how formulas are calculated can cause havoc for some "older" formulas. Here ...
Discover MoreTwo common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-01-29 03:22:15
Hans Kiesewetter
With a small remark. This function assumes the combinations of the same characters but in a different order all as the same combination. Just try "=Combin(4,4)" and the answer = "1". So ABCD and CABD are assumed to be the same combination.
2017-06-17 15:31:27
Peter
Dennis
I don't know if there is an Excel function for this case (other than using my formula and the FACT function).
Try the PERMUT function
2017-06-14 11:43:53
Dennis Costello
To be completely pedantic about it, there are several possibilities here:
- 4 character places, each of which can be any member of the set (i.e., duplicates allowed): the "license plate" use case. This is what Tony said, and his value is 36^4 (36 to the 4th power)
- 4 characters pulled from the set with no duplicates allowed and insensitive to the order: this is the "hand" case when dealing a hand from a deck of cards. As David pointed out, this is what Combin does, and it's equal to n! / ( (n-m)! * m! ), where n is 36 in this case and m is 4. This is n-factorial (the product all all integers 1, 2, 3, ..., n) divided by the factorial of n-m and then again by m-factorial. After the first division the result is 36 * 35 * 34 * 33 - the second division is by 4 * 3 * 2 * 1
- 4 characters pulled from the set, no duplicates, but the order matters: this would be n! / (n-m)! and there are 1,413,720 such possibilities. I don't know if there is an Excel function for this case (other than using my formula and the FACT function).
2016-05-19 17:48:17
Tony
As David stated the answer is for the single combination that he explained and only using a letter or number once. If you can use any combination and repeat letters and numbers it would be 1,679,616.
2016-05-14 11:46:48
David
It's worth noting that COMBIN gives the number of combinations possible *in any order*. In other words, it considers "abcd", "acbd", "dcba" etc. all as a single combination.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments