I'm working on a Google Sheets spreadsheet, and I'm having trouble figuring out a way to count all possible ways a set of (concrete) values could be sorted, taking into account possible duplicate values.

For example, if I have a list of 3 values A,A,B, I want to know how many ways I could shuffle them around. In this case the result would be (in no particular order):


so 3 total combinations.

I am trying to figure out what formula I could use to input such a list and get the total combinations. I've tried to build formulas using COMBIN, COMBINA, PERMUT and MULTINOMIAL, combined with COUNTUNIQUE and COUNTA, but so far none of the results match what I expect (3).

Is this such an uncommon need? I thought it would be a well-known formula with many uses. e.g. to calculate the theoretical maximum of anagrams of a word, or counting the number of possible states of a shuffled deck of cards, etc. Maybe I'm missing the proper name for this operation?

I tried reading the Wikipedia articles on Binomial coefficient and Permutation, but they are quite hard to read.

I am comfortable building formulas on Google Sheets once I have a mental picture of the required steps, but what's frustrating me is that I can intuitively make sense of the problem and obtain the solution manually by exhaustively enumerating all the possibilities, but can't figure out how to convert that process into a generic mathematical equation. Any hints?

  • Imagine A as Apple,B as Ball. In all 3sets,that you described, you end up with exactly 2 Apples and 1Ball. The number of combination is only 1. All 3 sets are not distinct. Is it permutation? Permutation,in general,does not allow repetition/duplicates(like A twice). This is more of a n-tuple.From a Set containing k elements(A,B),the number of possible n-tuples = k^n = 2^3 = 8. This k^n is only valid,Where no restrictions are placed on the repetition,i.e., (A,A,A),(B,B,A),(B,A,B),etc. are all possible. You're looking@ n-tuples with restriction on repetition on both elements(A:2,B:1)
    – TheMaster
    May 8, 2018 at 22:29
  • You're better off asking MathOverflow. If you find a answer, Do update this post.
    – TheMaster
    May 8, 2018 at 22:30


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.