The DATE function returns a number that acts as a date in the Excel environment. The image above shows you the DATE function in column E. It uses three arguments, the first argument is the year found in column B.

The second argument is the month, shown in column C. Note that you need to use a number for the month.

The third argument is the day of the month, displayed in column D. Make sure you enter the correct day for the month, keep in mind most months have 30 days or 31 days. February has 28 days except for leap years that have 29 days.

DATE(*year*, *month*, day)

* year *- A number equal or higher than 1900

* month *- A number between 1 (January) and 12 (December)

** day **- A number between 1 and 31

If you use a month number larger than 12, for example 13, DATE(2009,13,1) the DATE function returns 1/1/2010. Month number zero returns the previous month.

The same thing happens with a day number that is larger than 31 or smaller than 1.

You can use that to do dynamic date series or do date based calculations.

]]>**TEXT(***value*, *format_text***)**

Converts a value to text in a specific number format.

*value* - The string you want to format. You can use a cell reference here or use a text string.

*format_text* - Formatting code allowing you to change the way, for example, a date or a number is displayed to the Excel user.

The TEXT function lets you easily create your own custom formats, there are four parts shown in the picture below.

You can use the semicolon to separate different settings for different values, the zero is a character symbol (among others) for numbers and the @ is a character symbol for text values.

- Positive values
- Negative values
- Zero values
- Text values

You can ignore the semicolons if you want to format all values equal.

**. (period)** - Decimal point. All digits to the left of the decimal point are shown no matter how many formatting characters.

**, (comma)** - The comma separates numbers into three characters. This makes large numbers easier to read.

**0** - Digit symbol. Example, type 4.5 in a cell and the format_text argument is 0.00 then the output from the TEXT function is 4.50

**#** - Digit symbol. Does not show extra zeros. Example, type 4.5 in a cell and the format_text argument is #.## then the output from the TEXT function is 4.5

**?** - Digit symbol, same as # but aligns the decimal points in a column.

**%** - Percentage. The percentage symbol multiplies the value with 100.

**E+ E- e+ e-** - Scientific format.

**@** - Text characters

**"Text"** - Characters enclosed by quotation marks shows the characters.

**m** - Month number without a leading zero

**mm** - Month number with a leading zero when necessary

**mmm** - Month as an abbreviation, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov and Dec.

**mmmm** - Full month name.

**d**- Day as a number without a leading zero

**dd** - Day as a number with a leading zero when necessary

**ddd** - Day as an abbreviation, Sun, Mon, Tue, Wed, Thu, Fri and Sat.

**dddd** - Full day name.

**yy** - Year as a two-digit number.

**yyyy** - Year as a four-digit number.

**h** - Hour as a digit without a leading zero.

**[h]** - Shows more than 24 hours.

**hh** - Hour as a digit with a leading zero when needed.

**m** - Minute without a leading zero.

**[m]** - Shows more than 60 minutes.

**mm** - Minute with a leading zero when necessary.

**s** - Second without a leading zero.

**[s]** - Shows more than 60 seconds.

**ss** - Seconds with a leading zero when necessary.

**AM/PM** - Shows the time of 12-hour hours.

**am/pm** - See above.

**A/P** - See above.

**a/p** - See above.

**Note!** Check your regional settings if the format codes above don't work for you. Win 10 users can open the Control Panel and then click on "Regional settings" to view your settings.

Round numbers

Thousands and millions

Show leading zeros

Format positive, negative and zero values differently

Hide values

Custom groups

Fractions

Phone numbers

Indents

Singular / Plural

The number of digit symbols you use after the decimal determines which number you want to round.

You can round to thousands and millions and so on using the comma character.

The comma symbol scales the number by a thousand, two comma symbols scale the number by a million (1000 * 1000 = 1,000,000).

K = 1,000 and M = 1,000,000

The \ (backslash) symbol escapes the M character so Excel displays the letter M instead of converting the number into minutes.

The 0 (zero) symbol allows you to show leading zeros.

The semicolon character lets you specify formatting code for positive, negative, zero values and text values. The picture below shows you these settings: 0.0;-0.00;"Zero";@

The first position is formatting code for positive values, here I have chosen 0.0. All positive values are rounded to one decimal.

The second position is affecting negative values, in this case, all negative values are rounded to two decimals.

The third position changes 0 to Zero and the fourth position shows the text value.

Positive values are hidden if you leave out the first position.

Negative values are hidden if you leave the second position empty.

If you leave the third position empty zeros are hidden.

Omit the fourth position to hide text values.

Group 3 is assigned to values larger than 150, Group 2 is assigned to values larger than 100, Group 1 is assigned to all other values.

The division symbol lets you create fractions.

The 0 (zero) symbol lets you create phone numbers with leading 0's.

Use the underscore character _ and then the character you want to use as an indent. The character is not shown.

The example below uses - (minus) as the indent character in the first position (positive numbers) to align negative and positive numbers. The first row is not aligned.

]]>

SUMIF(*range*, *criteria*, [*sum_range*])

The SUMIF function sums values based on a condition. The condition can be applied to the values being summed or to corresponding values in another column.

*range* - The cell range you want to check the condition against

*criteria - *A single condition to filter values you want to sum

[*sum_range*] - This argument is optional. The *sum_range* argument allows you to apply a condition to corresponding values.

You are allowed to use logical operators like:

- < less than
- > greater than
- = equal to
- <= less than or equal to
- >= greater than or equal to

You also have the option to use an asterisk * to perform wildcard operations and a question mark ? to match a single character.

If you need to apply multiple conditions or criteria I recommend the SUMIFS function.

The wildcard character * allows you to match one to multiple characters and the question mark matches a single character.

Thank you *so* much for your detailed examples and actively replying to users! I have a problem, which I've tried solving by editing your formula examples for 20+ hours without a success although I thought I could do it myself but apparently not, so here goes:

I have a long list of organizations that work in specific zip areas defined by zip ranges (start and end). One org might have multiple zip ranges and there can be overlap between organizations (i.e. one zip might "belong" to >1 org). Then there's another list that has got all the possible existing zips. I would need to have all existing zips falling inside the zip range of the organization added to separate columns on the matching row of the first list.

First list:

org name | zip range start | zip range end

org 1 | 00100 | 00200

org 2 | 00180 | 00250

org 1 | 00220 | 00230

Second list:

00100

00110

00190

00220

00225

Desired result:

org name | zip1 | zip2 | zip3 | zip n...

org 1 | 00100 | 00110 | 00190

org 2 | 00190 | 00220 | 00225

org 1 | 00220 | 00225

Perfect result:

org name | zip1 | zip2 | zip3 | zip n...

org 1 | 00100 | 00110 | 00190 | 00220 | 00225

org 2 | 00190 | 00220 | 00225

This would be of HUGE help if you could solve the problem. Thank you very much already for all the help, your examples have provided me with tons of new Excel wizardry skills.

Best wishes,

Eero

Thank you for a great question. The first list is in cell range B2:F5, second list is in B7:B11.

**Formula in cell B15:**

=LOOKUP(2, 1/(COUNTIF($B$14:B14, $B$3:$B$5)=0), $B$3:$B$5)

Copy cell B15 and paste to cells below as far as needed. This formula extracts unique distinct values, you can read about the formula in more detail here:

How to extract a unique distinct list

**Array formula in cell C15:**

=SMALL(IF((IF($B15=$B$3:$B$5, $C$3:$C$5, "")<=TRANSPOSE($B$8:$B$12))*(IF($B15=$B$3:$B$5, $E$3:$E$5, "")>=TRANSPOSE($B$8:$B$12)), TRANSPOSE($B$8:$B$12), ""), COLUMNS($A$1:A1))

Copy cell C15 and paste to adjacent cells to the right and then to cells below. This formula has the ability to use more than one numerical range simultaneously as criteria.

Example, org 1 has two ranges 100-200 and 220-230. org 2 has only one numerical range, 180 -250.

**Step 1 - Filter start range values**

The IF function allows you to filter values, in this case a condition applied to cell range B3:B5 and return corresponding values in cell range C3:C5

IF($B15=$B$3:$B$5, $C$3:$C$5, "")

becomes

IF("org 1 "={"org 1 ";"org 2 ";"org 1 "},{100;180;220},"")

becomes

IF({"TRUE";"FALSE";"TRUE"},{100;180;220},"")

and returns the following array: {100; ""; 220}

**Step 2 - Compare with list**

IF($B15=$B$3:$B$5, $C$3:$C$5, "")<=TRANSPOSE($B$8:$B$12)

becomes

{100; ""; 220}<=TRANSPOSE($B$8:$B$12)

becomes

{100; ""; 220}<={100,110,190,220,225}

and returns {TRUE, TRUE, TRUE, TRUE, TRUE; FALSE, FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, TRUE, TRUE}

**Step 3 - Filter end range values**

IF($B15=$B$3:$B$5, $E$3:$E$5, "")

becomes

IF("org 1 "={"org 1 ";"org 2 ";"org 1 "},{200;"";230},"")

becomes

IF({"TRUE";"FALSE";"TRUE"},{200;"";230},"")

and returns the following array: {200;"";230}

**Step 4 - Compare with list**

IF($B15=$B$3:$B$5, $E$3:$E$5, "")>=TRANSPOSE($B$8:$B$12)

becomes

{200;"";230}>=TRANSPOSE($B$8:$B$12)

becomes

{200;"";230}>={100,110,190,220,225}

and returns {FALSE, FALSE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE}

**Step 5 - Filter values**

Multiplying both logical expressions gives this formula:

IF((IF($B15=$B$3:$B$5, $C$3:$C$5, "")<=TRANSPOSE($B$8:$B$12))*(IF($B15=$B$3:$B$5, $E$3:$E$5, "")>=TRANSPOSE($B$8:$B$12)), TRANSPOSE($B$8:$B$12), "")

becomes

IF({TRUE, TRUE, TRUE, TRUE, TRUE; FALSE, FALSE, FALSE, FALSE, FALSE; FALSE, FALSE, FALSE, TRUE, TRUE}*{FALSE, FALSE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE}, TRANSPOSE($B$8:$B$12), "")

becomes

IF({1,1,1,0,0;0,0,0,0,0;0,0,0,1,1}, TRANSPOSE($B$8:$B$12), "")

becomes

IF({1,1,1,0,0;0,0,0,0,0;0,0,0,1,1}, {100,110,190,220,225}, "")

and returns this array:

{100,110,190,"","";"","","","","";"","","",220,225}

The following picture shows an index table that has values horizontally and what range they are in.

Example, values in range 100-200 are 100, 110 and 190 because the array has number 1 (TRUE) in those locations.

**Step 6 - Find the k-th s****mallest value**

SMALL(IF((IF($B15=$B$3:$B$5, $C$3:$C$5, "")<=TRANSPOSE($B$8:$B$12))*(IF($B15=$B$3:$B$5, $E$3:$E$5, "")>=TRANSPOSE($B$8:$B$12)), TRANSPOSE($B$8:$B$12), ""), COLUMNS($A$1:A1))

becomes

SMALL({100,110,190,"","";"","","","","";"","","",220,225}, COLUMNS($A$1:A1))

becomes

SMALL({100,110,190,"","";"","","","","";"","","",220,225}, 1)

and returns 100 in cell C15.

The SUM function in cell D3 uses only a single cell reference and still manages to sum current and previous values in column D. Read on to find out how.

**The formula in cell D3:**

=SUM($C$3:C3)

The SUM function has a cell reference that consists of two parts, the first part has a dollar sign before the column character and another one before the row number.

The dollar sign tells you that the cell reference is locked and won't change if you copy the formula. In Excel terminology: an absolute cell reference.

However, the second part of the cell reference has no dollar signs and that part changes when you copy and paste the formula to other cells. In Excel terminology: a relative cell reference.

What happens when you copy the cell and paste to cell D4?

=SUM($C$3:C4)

The second part of the cell reference now points to cell C4 and the first part is still pointing to cell C3. The cell reference expands as you copy the formula to cells below.

If you want to learn more about absolute and relative cell references, read the following article:

**How to use absolute and relative references in excel**

Comments(12) Filed in category: Excel

The following article explains Excel's SUM function in greater detail:

The SUM function in excel allows you to add values, the function returns the sum in the cell it is […]

Comments(0) Filed in category: Excel, SUM function

The formula above in column D calculates running totals based on a condition. The condition changes depending on the value in column B.

**Example 1**, in cell D6 the formula calculates the sum for Item C. Item C is only in cell B6, the corresponding value in column C is B6. The formula returns 2000 in cell D6.

**Example 2**, in cell D7 the formula calculates the sum for Item A. Item A is in cell B3, B4 and B7, the corresponding values in column C are C3,C4 and C7.

4000 + 6000 + 1000 = 11000

The formula returns 11000 in cell D7. There are several ways to calculate a running total based on a condition, the easiest and smallest formula is probably the SUMIF function.

**Formula in cell D3:**

=SUMIF($B$3:B3,B3,$C$3:C3)

The arguments in a SUMIF function are: **SUMIF(***range*, *criteria*, [*sum_range*]**)**

The *range* argument* *grows when the formula is copied to cells below. $B$3:B3 changes to $B$3:B4 when the formula is copied to cell D4. This applies to the [*sum_range*] argument as well.

The expanding cell references make this formula include more and more cells and allowing it to calculate running totals based on a condition.

**Formula in cell D3:**

=SUMPRODUCT((TEXT(B3, "YYYY-MM")=TEXT($B$3:B3, "YYYY-MM"))*$C$3:C3)

The dates in column B are sorted in an ascending order, however, the formula works fine for dates sorted in a descending order as well.

The formula in column D adds amounts to a running total using the corresponding date as a condition.

Example, the formula in cell D6 uses this text string "2017-10" in cell B6 as a condition to add all previous amounts above cell D6 that also return text string "2017-10".

In other words, the formula creates a running total for the current month and starts all over when a new month begins.

Note that I am explaining the formula in cell D8, not cell D3.

**Step 1 - Convert corresponding date to a text string**

TEXT(B8, "YYYY-MM") returns 2017-11

In this case, the TEXT function converts a date to a particular format specified in the second argument. YYYY returns the year and MM returns the month number.

**Step 2 - ***Co*nvert corresponding dates to text strings

TEXT($B$3:B8, "YYYY-MM") returns the following array {"2017-10";"2017-10";"2017-10";"2017-10";"2017-11";"2017-11"}

The first argument in the TEXT function is a cell range containing multiple values. The TEXT function returns an array with the exact same number of values.

**Step 3 - Build a logical expression**

TEXT(B8, "YYYY-MM")=TEXT($B$3:B8, "YYYY-MM")

becomes

2017-11={"2017-10";"2017-10";"2017-10";"2017-10";"2017-11";"2017-11"}

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

**Step 4 - Multiply with amounts**

(TEXT(B8, "YYYY-MM")=TEXT($B$3:B8, "YYYY-MM") )*$C$3:C8

becomes

{FALSE; FALSE; FALSE; FALSE; TRUE; TRUE}*{70;170;210;280;60;160;240}

and returns {0; 0; 0; 0; 60; 100}

**Step 5 - Sum values**

SUMPRODUCT((TEXT(B3, "YYYY-MM")=TEXT($B$3:B3, "YYYY-MM"))*$C$3:C3)

becomes

SUMPRODUCT({0; 0; 0; 0; 60; 100})

and returns 160 in cell D8.

Recommended article:

**Running totals within date range**

Comments(9) Filed in category: Excel, SUMPRODUCT function

**Formula in cell D3:**

=IF(TEXT(B3, "YYYY-MM")<>TEXT(B4, "YYYY-MM"), SUMPRODUCT(((TEXT(B3, "YYYY-MM"))=(TEXT($B$3:B3, "YYYY-MM")))*$C$3:C3), "")

The picture above shows a formula in column D that shows the running total if the next cell in column B contains a new month.

Note, I will explain the formula in cell D7. The cell references have changed, the formula now looks like this:

=IF(TEXT(B7, "YYYY-MM")<>TEXT(B8, "YYYY-MM"), SUMPRODUCT(((TEXT(B7, "YYYY-MM"))=(TEXT($B$3:B7, "YYYY-MM")))*$C$3:C7), "")

Why will I explain the formula in cell D7? Not much is happening in D3, D4, D5 and D6.

**Step 1 - Check if month is not equal to month in cell below**

In this example, the TEXT function converts a date to a particular format specified in the second argument. YYYY returns the year and MM returns the month number.

TEXT(B7, "YYYY-MM")<>TEXT(B8, "YYYY-MM")

becomes

"2017-10"<>"2017-11"

and returns TRUE.

**Step 2 - Convert corresponding date to a text string**

TEXT(B7, "YYYY-MM") returns 2017-10

**Step 3 - ****Convert corresponding dates to text strings**

TEXT($B$3:B7, "YYYY-MM") returns the following array {"2017-10";"2017-10";"2017-10";"2017-10";"2017-10"}

The first argument in the TEXT function is a cell range containing multiple values. The TEXT function returns an array with the exact same number of values.

**Step 4 - Build a logical expression**

TEXT(B7, "YYYY-MM")=TEXT($B$3:B7, "YYYY-MM")

becomes

2017-10={"2017-10";"2017-10";"2017-10";"2017-10";"2017-10"}

and returns {TRUE; TRUE; TRUE; TRUE; TRUE}

**Step 5 - Multiply with amounts**

(TEXT(B7, "YYYY-MM")=TEXT($B$3:B7, "YYYY-MM") )*$C$3:C7

becomes

{TRUE; TRUE; TRUE; TRUE; TRUE}*{70;100;40;60;70}

and returns {70;100;40;60;70}

**Step 6 - Sum values**

SUMPRODUCT((TEXT(B7, "YYYY-MM")=TEXT($B$3:B7, "YYYY-MM"))*$C$3:C7)

becomes

SUMPRODUCT({70;100;40;60;70})

and returns 340 in cell D8.

Is it possible to quickly select all empty cells and then sum cells above to next empty cell?

Can I have a formula in grand total (row 18) that only sums all the totals above?

Select all values and the blank total cells.

Press F5 or go to tab "Home" on the ribbon, click on "Find & Select" button and then click on "Go To.."

Click on "Special..." button.

Click on "Blanks" and then on "OK" button.

Go to tab "Home" on the ribbon and click on "AutoSum" button.

All empty cells now have a SUM formula that adds all above values to the next SUM formula.

Select cell C18 and type this formula:

=SUMPRODUCT(ISFORMULA(C3:C17)*C3:C17)

Press Enter. Copy cell C18 and paste to cell range D18:F18.

ISFORMULA(C3:C17) checks if a cell in cell range C3:C17 has a formula. It returns TRUE or FALSE.

ISFORMULA(C3:C17) returns this array: {FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; TRUE}

The picture below shows this array in column D.

The array shows that there is a formula in C5, C9, C12 and C17.

ISFORMULA(C3:C17)*C3:C17 multiplies the boolean values with their corresponding values in column C, shown in column E below.

The SUMPRODUCT function then sums all values in the array. Why not use the SUM function? You need to enter it as an array formula if you use the SUM function.

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired output from a database or copying values from an HTML file.

The INDEX function allows you to easily rearrange values on an Excel worksheet, in this case, data seems to be grouped record by record. 4 values in one record.

Column D to G shows you the INDEX function rearranging the data.

The INDEX function has three arguments: INDEX(*reference*, *row_num*, *[column_num]*)

The reference in the first argument points to cell range B2:B17. To return a value from that cell range you must know where it is, the INDEX function uses a row and column number to locate a particular value. Since this cell range (B2:B17) only has one column you only need to use a row number to get the value you want.

The picture above has relative row numbers in column A to show you what number the INDEX function needs for it to return a specific value from cell range B2:B17. The values in column B seems to repeat, every 4th row has a company name. We can use that information to build the formula that returns a record in a row each.

The formula needs numbers in a determined sequence depending where on the worksheet it is. If I enter the formula in cell D2 it must get values in the order shown in the picture below.

Combining the ROWS function and the COLUMNS function lets you build the number sequence shown above.

**Formula in cell D2:**

=INDEX($B$2:$B$17,COLUMNS($A$1:A1)+ROWS($A$1:A1)*4-4)

COLUMNS($A$1:A1) returns 1 and ROWS($A$1:A1)*4-4 returns 0 (zero). 1 +0 is 1. The INDEX function returns the first value in cell range B2:B17 which is "Company".

In cell E2 COLUMNS($A$1:A2) returns 2 and ROWS($A$1:A1)*4-4 returns 0 (zero). 2+0 is 2. The INDEX function returns the second value in cell range B2:B17 which is "Item".

In cell D3 COLUMNS($A$1:A1) returns 1 and ROWS($A$1:A2)*4-4 returns 4. 1+4 is 5. The INDEX function returns the fifth value in cell range B2:B17 which is "Vegetables are us".

This article explains how to set up a workbook so a macro is executed every time you open the workbook. Excel **event code** makes this possible and it is easier than you think.

For example, you could have a macro that opens another workbook that you need, saving you time, making you happy and feeling awesome.

The following macro opens workbook *Book1.xlsx* in folder *c:\temp*

Sub Macro1 Workbooks.Open ("c:\temp\Book1.xlsx") End Sub

Copy the macro code above and go to tab "Developer" on the ribbon. If it is missing search the internet for your Excel version and "Show developer tab".

Click on "Visual Basic" button to open the **Visual Basic editor**. Right-click on your workbook in the **Project Explorer** window.

Click on "Insert" and then on "Module". This action adds a **code module** to your workbook.

Now paste the VBA code above to your code module.

Go back to Excel. Go to tab "Developer" and click on "Macro" button. The following dialog box appears:

Click on "Run" button and the workbook is opened.

The following article shows you how to open multiple excel files using checkboxes:

**Open Excel files in a folder [VBA]**

Comments(38) Filed in category: Check-boxes, Excel, Files and folders

Press Alt+F11 to open the VB Editor or go to tab "Developer" on the ribbon, click on "Visual Basic" button. Double click on "This Workbook", if you can't see it expand the list by clicking on the + sign.

Paste the following event code to the workbook module:

Private Sub Workbook_Open() Macro1 End Sub

Go back to Excel. Save the workbook with file extension *.xlsm, this is important.

Close workbook and open it again. The workbook Book1.xlsx in folder c:\temp is now automatically opened. (If it exists..)

]]>The list in column D contains only values that are unique, Aa and BB exists only once in the list, all other values have a duplicate.

The formula extracts CC, bb, aa and Cc because they exist only once in column B.

**Array formula in cell D3:**

=INDEX($B$3:$B$15, SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1)))

If you are looking for a unique distinct list, read this post: Extract unique distinct values (case sensitive) [Formula].

This post explains how to do a case-sensitive VLOOKUP and returning multiple values:

**Search case sensitive and return multiple values**

Comments(0) Filed in category: Case sensitive, Excel, VLOOKUP and return multiple values

Make sure you read the following article if you want to extract duplicate values:

**Extract a list of duplicates from a column**

Comments(41) Filed in category: Duplicate values, Excel

- Copy (Ctrl + c) above formula
- Double click on cell C2
- Paste (Ctrl + v) to cell C2
- Press and hold CTRL + SHIFT simulatneously
- Press Enter once
- Release all keys

Your formula now looks like this: {=array_formula}

Don't enter the curly brackets, they appear automatically.

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Count values, Excel

You can easily follow along if you download the attached file and select cell D3. Then go to tab "Formulas" on the ribbon and click "Evaluate Formula" button.

Click "Evaluate" button shown in above picture to move to next step.

The EXACT function is case sensitive function that allows you to compare values. If they match EXACT returns TRUE, if not FALSE.

EXACT($B$3:$B$15,TRANSPOSE(B$3:$B$15))*1

If we use TRANSPOSE we can compare values against each other to build an array, in a single calculation. The following picture shows you this array as an index table, I have highlighted cells that match green. Example, cell C3 shows you the result of a comparison between the value in cell C2 and B3. Since it is the same value they must match and the formula returns 1 and is highlighted green.

It is now obvious that value Aa has a duplicate because cell J3 is also highlighted green.

Incredible that Excel allows you to do such a complicated calculation in a single cell.

Recommended reading

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Comments(4) Filed in category: Excel, Transpose

The MMULT function is like the SUMPRODUCT function but on steroids, let me explain. SUMPRODUCT lets you multiply and then sum values, the result is a single value.

MMULT lets you multiply and sum values either column-wise or row-wise, the result is an array.

MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0) is entered in column P, see picture below.

It is now easy to spot unique values in the index table, if column P contains 1 the corresponding value in column B must be unique.

Recommended reading

**MMULT function – Matrix multiplication**

Comments(4) Filed in category: Excel, MMULT function

Column Q shows you corresponding relative row number if value in column P is equal to 1.

IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), "")

returns this array: {"";2;"";""; "";"";7; "";9;"";11; "";""}

Recommended reading

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel

SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1))

becomes

SMALL({"";2;"";""; "";"";7; "";9;"";11; "";""},ROWS($A$1:A1))

becomes

SMALL({"";2;"";""; "";"";7; "";9;"";11; "";""},1)

and returns 2.

Recommended reading

**SMALL function and LARGE function**

Comments(12) Filed in category: Excel

INDEX($B$3:$B$15, SMALL(IF(MMULT(EXACT($B$3:$B$15, TRANSPOSE(B$3:$B$15))*1, ROW($B$3:$B$15)^0)=1, MATCH(ROW($B$3:$B$15), ROW($B$3:$B$15)), ""),ROWS($A$1:A1)))

becomes

INDEX($B$3:$B$15, 2)

becomes

INDEX({"Aa"; "CC"; "AA"; "BB"; "BB"; "EE"; "bb"; "Aa"; "aa"; "AA"; "Cc"; "EE"; "BB"}, 2)

and returns "CC" in cell D3.

Recommended reading

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

The image below shows a table in column B and C. Column B contains names and column C contains products. How many unique distinct products did Salesperson Jennifer sell?

The blue arrows shows unique distinct products based on sales person "Jennifer", the total number match with the number in cell E4. The remaining highlighted records are only duplicate values.

**Array formula in cell E2:**

=SUM(IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0))

Learn to build formulas that count unique distinct values based on criteria:

**Count unique distinct values that meet multiple criteria**

Comments(93) Filed in category: Count unique distinct values, Excel

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Recommended article:

**Learn the basics of Excel arrays**

Comments(2) Filed in category: Count values, Excel

You can follow along as I explain the formula, select cell E2. Go to tab "Formulas" on the ribbon, click on "Evaluate formula" button.

Click on "Evaluate" button shown in above image to move to next step.

1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26))

becomes

1/{4; 2; 3; 4; 0; 3; 4; 3; 4; 3; 0; 3; 2; 3; 2; 2; 2; 4; 4; 4; 4; 3}

and returns

{0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}

Recommended article:

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Comments(1) Filed in category: COUNTIFS function, Excel

IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0)

becomes

IF({TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}, 0)

and returns

{0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0}

Recommended article:

Comments(9) Filed in category: Excel

=SUM(IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0))

becomes

=SUM({0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0})

and returns 4 in cell E2.

Recommended article:

Comments(0) Filed in category: Excel, SUM function