Basic Formulas | Advanced Formula

Table of Contents

How do I compare two different lists?

The following articles demonstrate how to extract values that either is shared by both columns or not:

How to extract not shared values in two columns
Extract shared values between two columns
What values are missing in List 1 that exists i List 2?
Compare two columns and return differences
Compare two columns in different worksheets

Back to top

How do I compare two different data sets?

A dataset is a group of related values, often each row contains values that form a record. Data organized vertically is not that common.

How to extract differences between price lists
Filter records occurring only in one table
Filter shared records from two tables

Back to top

How can I extract values shared/not shared between two cell ranges?

The cell range in the articles below contains values that are NOT organized by row, in other words, they are not related.

Filter common values between two ranges
Filter values occurring in range 1 but not in range 2

Back to top

How can I automatically plot date ranges in a schedule?

This article Shift Schedule explains how to show date ranges in a schedule using formulas. You can also do the opposite, Get date ranges from a schedule using a formula.

The following article demonstrates a calendar that highlights events: Yet another excel calendar or: Highlight events in a calendar I recommend that you check out the Calendar category.

Back to top

How can I find overlapping date ranges?

The easiest way to check if a date range is overlapping another date range is shown in the following article: Identify overlapping date ranges

This article shows how to highlight overlapping date ranges based on a condition: Highlight records based on overlapping date ranges and a condition

This article explains how to calculate overlapping days between two date ranges: Days contained in a range that overlap another range

To count overlapping days across multiple date ranges, see these articles:
Count overlapping days
Count overlap. days(1)
Count overlap. days(2)

Check out the overlapping category for more articles.

Back to top

How can I identify overlapping time ranges?

Use MEDIAN function to calculate overlapping ranges

Back to top

How can I automatically create date ranges?

Follow the instructions on this web page: Create a date range [Formula]

Back to top

How do I combine or merge two columns?

Merge two columns

Back to top

How do I combine or merge three columns?

Merge three columns into one list

Back to top

How do I combine or merge two tables?

Merge tables based on a condition

Back to top

How do I count a pattern in a cell value?

Count a given pattern in a cell value

Back to top

How do I count unique distinct values?

Count unique distinct values

Count unique distinct values based on a condition

Count unique distinct values that meet multiple criteria

Back to top

How do I count unique values?

A unique value is a value that exists only once in a list, for example, value Pear exists only once in the list above so that value is counted as a unique value.

There are three unique values in the list above: Pear, Banana and Lemon. All other values has at least one duplicate value.

Formula in cell E3:

=SUMPRODUCT((COUNTIF($B$3:$B$10,$B$3:$B$10)=1)*1)

Explaining formula in cell E3

Step 1 - Count each value

The COUNTIF function counts the number of cells that equals a given value, however, if we use multiple values the function returns an array of values containing the number of values for each given value.

COUNTIF($B$3:$B$10,$B$3:$B$10)

becomes

COUNTIF({"Apple"; "Pear"; "Apple"; "Orange"; "Apple"; "Banana"; "Orange"; "Lemon"},{"Apple"; "Pear"; "Apple"; "Orange"; "Apple"; "Banana"; "Orange"; "Lemon"})

and returns {3; 1; 3; 2; 3; 1; 2; 1}.

Step 2 - Identify unique values

We know that a value is a unique value if the corresponding number in the array is equal to 1. The equal sign allows us to compare each number in the array. It returns the boolean value TRUE or FALSE.

COUNTIF($B$3:$B$10, $B$3:$B$10)=1

becomes

{3; 1; 3; 2; 3; 1; 2; 1}=1

and returns {FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE}.

Step 3 - Multiply with 1

The SUMPRODUCT function can't add boolean values so we must convert the boolean values to their numerical equivalents 1 and 0 (zero).

The parentheses let you change the order of calculation, we want to multiply the array with 1 and nothing else.

(COUNTIF($B$3:$B$10,$B$3:$B$10)=1)*1

becomes

({FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE})*1

and returns {0;1;0;0;0;1;0;1}.

Step 4 - Add values

SUMPRODUCT((COUNTIF($B$3:$B$10,$B$3:$B$10)=1)*1)

becomes

SUMPRODUCT({0;1;0;0;0;1;0;1})

and returns 3.

Back to top

How do I count duplicate values?

Back to top

How do I count cells containing text from list?

Back to top

How do I sort values using a formula?

Back to top

How do I count repetead contiguous values

Count contiguous values

Back to top

How do I extract unique distinct values?

Back to top

How do I extract duplicate values?

Back to top

How do I extract unique distinct records?

Back to top

How do I extract duplicate records?

Back to top