exceljet.net

Combinations and permutations both count how many ways you can choose items from a group, but they answer different questions. Permutations count arrangements where order matters; combinations count selections where order does not. In Excel you calculate them with separate functions — PERMUT and COMBIN (plus PERMUTATIONA and COMBINA when repetition is allowed) — and the choice between them depends entirely on whether swapping two chosen items produces a different result.

The core distinction: does order matter?

Ask one question before picking a function: if I rearrange the same items, is that a new outcome?

• Order matters → permutation. Example: a 3-digit lock code. 1-2-3 and 3-2-1 are different codes.
• Order doesn't matter → combination. Example: a 3-person committee drawn from 10 people. The same three people form the same committee no matter what order you list them.

A second question decides which variant you need: can an item be chosen more than once?

Excel functions for each case

PERMUT — ordered, no repeats

=PERMUT(number, number_chosen) returns the number of ordered arrangements of number_chosen items taken from number distinct items, with no item used twice.

Example: how many ways can 3 runners finish first, second, and third out of 8?

=PERMUT(8,3)   → 336

Each finishing order is a distinct outcome, and a runner can't hold two places.

COMBIN — unordered, no repeats

=COMBIN(number, number_chosen) returns the number of ways to choose number_chosen items from number items when order is irrelevant.

Example: how many 5-card hands can be dealt from a 52-card deck?

=COMBIN(52,5)   → 2,598,960

A hand is the same set of cards regardless of the order dealt.

PERMUTATIONA and COMBINA — repetition allowed

These variants allow an item to be picked again.

• =PERMUTATIONA(10,3) → 1000. This is the count of 3-character codes from 10 symbols where repeats are allowed (10 × 10 × 10).
• =COMBINA(n,k) counts unordered selections where repeats are permitted.

Use these when the same value can appear more than once in a result — for example, a password that may repeat a character, or a lottery format that allows a number to recur.

A worked comparison

Suppose you have 5 distinct items and you choose 2.

• Ordered, no repeats (PERMUT): =PERMUT(5,2) → 20
• Unordered, no repeats (COMBIN): =COMBIN(5,2) → 10

The permutation count is exactly double the combination count here, because each unordered pair can be arranged in 2 orders. That relationship is general: PERMUT(n,k) = COMBIN(n,k) × FACT(k), where FACT(k) is the number of ways to order the k chosen items.

When to use which

• Passwords, PINs, lock codes, race finishes, seating in assigned seats → order matters → PERMUT / PERMUTATIONA.
• Lottery draws, card hands, committees, teams, choosing toppings → order doesn't matter → COMBIN / COMBINA.
• Repetition allowed (a digit or symbol can repeat) → the ...A variants.

Common pitfalls

• Using COMBIN when order matters undercounts by a factor of FACT(k). If your answer looks too small by exactly that factor, you likely picked the wrong function.
• Forgetting the repetition question. PERMUT and COMBIN assume no repeats; if repeats are allowed, your result will be too low.
• Confusing the arguments. Both functions take (number, number_chosen) — the total pool first, then how many you pick.
• Non-integer or negative inputs return errors, since these functions count discrete selections.

Exceljet's own coverage groups these under counting and probability formulas, alongside related functions like FACT, and its featured formulas show the same pattern of building a count from a clear rule about order and repetition. Once you've settled those two questions, the function choice follows directly.

The UNIQUE function extracts the distinct values from a range or array and returns them as a dynamic array. It removes duplicates, is not case-sensitive, and works with text, numbers, dates, and other data types. Because the result is a dynamic array, it automatically updates when the source data changes — you don't need to re-enter or drag the formula.

How UNIQUE works

You give UNIQUE a range or array, and it returns one instance of each distinct value it finds. The output "spills" into the cells below (and to the right, if the input is multi-column), so the formula lives in a single cell but fills a block of results.

Key behaviors:

• Duplicates collapse to one entry. If a value appears five times, it appears once in the output.
• Not case-sensitive. "Apple" and "apple" are treated as the same value, so only one survives.
• Works across data types. Text, numbers, dates, and mixed arrays are all handled.
• Dynamic. Edit or add to the source range, and the spilled result resizes on its own.

Basic usage

The simplest form takes a single argument — the range or array to deduplicate:

=UNIQUE(A2:A100)

Input: the range A2:A100. Action: UNIQUE scans the range and keeps one copy of each distinct value. Expected result: a vertical list of unique values that spills downward from the cell holding the formula.

If the source is a two-column range like A2:B100, UNIQUE returns unique rows (combinations), not unique values per column.

Optional arguments

UNIQUE accepts two optional arguments after the array:

For example, to list only values that occur exactly once:

=UNIQUE(A2:A100,,TRUE)

The empty middle argument keeps the default row comparison while setting exactly_once to TRUE.

Practical example

Suppose column A holds a list of customer cities with many repeats. =UNIQUE(A2:A100) in cell D2 produces a clean, de-duplicated list in D2 downward. Add a new city to column A, and it appears in the spilled list automatically — no copying or re-running needed.

Common points to watch

• Spill range must be empty. If cells below or beside the formula already contain data, you'll get a #SPILL! error. Clear the blocking cells.
• Dynamic arrays require a compatible Excel version. UNIQUE is part of the dynamic array family, alongside functions like SORT and FILTER.
• Case differences are ignored, so if you need case-sensitive uniqueness, UNIQUE alone won't distinguish "Apple" from "apple."

UNIQUE is best thought of as a live, self-updating de-duplication tool: point it at a range, and it keeps a clean distinct list in sync with your data.

Exceljet is an online learning resource for Excel users who want to work faster in the program. It combines short videos, worked formula examples, and a reference library of functions and shortcuts, so you can look up a specific technique or build understanding topic by topic. It is most useful when you already know the basics of Excel and want practical, example-driven answers rather than a structured certification course.

What you'll find on the site

The homepage organizes content into a few recurring blocks, each serving a different kind of need:

How the formula examples work

Formula pages follow a consistent pattern: a stated problem, a sample dataset, the working formula, and an explanation of how it evaluates. For example, the "count birthdays by year" example uses:

=SUMPRODUCT(--(YEAR(data[Birthday])=E5))

where data is an Excel Table. The page shows the formula in context, names the functions involved (SUMPRODUCT, YEAR), and explains the logic. This format lets you adapt the approach to your own data rather than copy it blindly.

Function and feature reference

Individual function pages describe behavior you need to know before relying on a function. The UNIQUE function page, for instance, notes that it extracts unique values from a range or array, returns a dynamic array that updates automatically when source data changes, is not case-sensitive, and works with text, numbers, dates, and other data types. Details like these determine whether a function fits your case.

Topic coverage

The category index spans a wide range of Excel work, including:

• Formulas — count, sum, average, lookup, if, grouping, rank, round
• Dynamic arrays — min/max, unique, sort, and related techniques
• Date and time — date series, WORKDAY, and similar functions
• Text, financial, percentage, random, range, and error handling
• Tables and filters, pivot tables, data validation, conditional formatting
• Shortcuts — navigation, selection, entering data, formatting, grid operations, dialogs

This breadth means the site functions as both a lookup tool and a browsing resource.

When Exceljet fits your needs

Exceljet is a good match if you want to:

• Find a ready-made formula pattern for a specific task
• Check how a function behaves before using it in a real workbook
• Learn a shortcut or technique in a few minutes
• Browse a topic area to see what's possible

It is less suited to users seeking a sequential beginner course, formal certification, or instructor-led training. The site presents itself as a way to work faster in Excel through clear examples and bite-sized videos, and its structure reflects that goal: problem-first, example-driven, and organized for quick reference.

One user testimonial on the site describes automating a budgeting issue in their industry that "has not been done in approximately 50yrs," crediting the resource. That kind of outcome depends on applying the examples to your own context, which is exactly how the material is designed to be used.

Use a formula that tests the year of each birthday and counts the matches. Exceljet's featured example does this with SUMPRODUCT and YEAR:

=SUMPRODUCT(--(YEAR(data[Birthday])=E5))

This works when your birthdays are stored in an Excel Table named data, the birthday column is Birthday, and the year you want to count is in cell E5. The formula returns the number of birthdays that fall in that year.

How the formula works

The formula runs in three stages:

1. YEAR(data[Birthday]) extracts the year from every date in the Birthday column, producing an array of years.
2. =E5 compares each of those years to the target year in E5, producing an array of TRUE and FALSE values.
3. The double negative -- converts TRUE to 1 and FALSE to 0, and SUMPRODUCT adds those 1s and 0s together.

The result is a single count. Because the reference is a structured table column, the formula expands automatically as you add rows.

Step-by-step setup

1. Format your source data as an Excel Table (select the range, then Insert > Table) and give it a name such as data.
2. Make sure the birthday column contains real dates, not text. YEAR only works on date values.
3. In a separate cell, enter the year you want to count, for example 1990 in E5.
4. Enter the formula:

=SUMPRODUCT(--(YEAR(data[Birthday])=E5))

5. Copy the formula down next to a list of years to get a count for each one.

Verify the result

• Check that the total of all year counts equals the number of rows in your table.
• Spot-check a few birthdays manually against the source dates.
• If a count looks wrong, confirm the birthday cells are dates and not text. A quick test is =ISNUMBER(data[Birthday]), which should return TRUE.

Common issues

• Text dates. Dates imported as text won't be recognized by YEAR. Convert them with Date > Text to Columns or by re-entering them in a date format.
• Wrong column name. The structured reference data[Birthday] must match the actual table and column names exactly.
• Blank cells. Empty birthday cells are treated as year 1900 by YEAR, which can inflate counts for that year. Filter or exclude blanks if this matters.
• Using a plain range instead of a table. You can replace data[Birthday] with a normal range like $C$5:$C$16, but you lose the automatic expansion when rows are added.

When to use this approach

This pattern is a good fit when you want a count per year without helper columns and your data lives in an Excel Table. If you need to count between two dates rather than by a single year, Exceljet also covers counting cells between dates, which uses a similar conditional-count structure.

Exceljet is a learning site for Microsoft Excel built around short, example-driven material: formula walkthroughs, bite-sized videos, function references, and articles organized by topic. It suits people who want to see a working formula and its explanation rather than read a full textbook, and it covers everything from basic counting and summing to dynamic arrays, lookup, dates, pivot tables, and shortcuts.

Formula examples

The Featured Formulas section shows a concrete formula alongside the problem it solves. A representative example is counting birthdays by year, which uses SUMPRODUCT and YEAR:

=SUMPRODUCT(--(YEAR(data[Birthday])=E5))

Here data is an Excel Table, and the formula counts rows whose birthday year matches the value in E5. Other examples in this section include counting cells between dates and testing whether a cell contains a number. Each entry follows the same pattern: a stated task, the formula, and an explanation of how it works.

Short video tutorials

Featured Videos are short clips, typically two to four minutes, each focused on one technique. Examples listed on the site include:

• How to generate random times
• How to generate random dates
• How to perform a random sort
• How to use SUMIFS with an Excel Table

The short format works well when you want to see a technique performed rather than read about it, and the narrow scope means you can watch one clip to solve one problem.

Function references

Featured Functions explains individual Excel functions, what they return, and how they behave. Examples include:

These pages are useful when you know which function you need but want to confirm its arguments or edge-case behavior.

Articles and topic browsing

Recent Articles covers broader subjects that don't fit a single formula or function. Examples include combinations and permutations in Excel (with FACT and PERMUT), arrays in Excel, replacing the DATEDIF function, and a seeded random number generator. There is also a shortcuts article on the ones worth knowing.

Topics By Category organizes the whole library so you can browse by area rather than search. The categories include:

• Formulas — count, sum, average, dynamic array, min and max, lookup, if, grouping, rank, round, date and time, text, financial, percentage, random, range, tables, errors
• Shortcuts — file, ribbon, general, tables and filters, drag and drop, navigation, selection, active cell, extend selection, select special, cell edit mode, entering data, formatting, number formatting, borders, formulas, grid operations, pivot tables, workbook, dialog boxes
• Functions — logical, date and time, lookup and reference, text, dynamic array, engineering, financial, information, math, trigonometry, statistical, web, database, user

How to choose where to start

If you have a specific task, start with a formula example or a function page. If you want to watch a technique, use the videos. If you are learning an area systematically, browse Topics By Category. The site also carries user testimonials, including one from a user who automated a long-standing budgeting problem in their industry.