Here are some functions that I used quite often.

1)  Two functions working together

=IFERROR(VLOOKUP(A2,STOCK,5,FALSE),0)

To explain

IFERROR(...,0) will give you a 0 when an error occurs (ie no match was found), you can use any character if you wish but zero is good for doing math.

VLOOKUP(A2,STOCK,5,FALSE)  this will use cell A2 as the key to be found in table "STOCK" (I normally place this on a separate sheet) and will return the 5th cell and FALSE is to have an exact match.

Example table STOCK

Stock No.	Make	Model	Engine No.	Cost
466414	AUDI	A4 B8	CG42720	68800.00
469686	AUDI	A4 B8 2.7	CG45852	63600.00

 

Please ensure that in your table you have no duplicates as VLOOKUP will only find the first occurrence of the value in cell A2

Good practice is to create a pivot table from your data and make that the table you use as this will ensure no duplicates.

 

2)  CONCATENATE

To use these examples in Excel, copy the data in the table below, and paste it in cell A1 of a new worksheet.

Data
brook trout	Andreas	Hauser
species	Fourth	Pine
32
Formula	Description
=CONCATENATE("Stream population for ", A2, " ", A3, " is ", A4, "/mile.")	Creates a sentence by joining the data in column A with other text. The result is Stream population for brook trout species is 32/mile.
=CONCATENATE(B2, " ", C2)	Joins three things: the string in cell B2, a space character, and the value in cell C2. The result is Andreas Hauser.
=CONCATENATE(C2, ", ", B2)	Joins three things: the string in cell C2, a string with a comma and a space character, and the value in cell B2. The result is Andreas, Hauser.
=CONCATENATE(B3, " & ", C3)	Joins three things: the string in cell B3, a string consisting of a space with ampersand and another space, and the value in cell C3. The result is Fourth & Pine.
=B3 & " & " & C3	Joins the same items as the previous example, but by using the ampersand (&) calculation operator instead of the CONCATENATE function. The result is Fourth & Pine.

 

Use the ampersand & character instead of the CONCATENATE function.	The ampersand (&) calculation operator lets you join text items without having to use a function. For example,=A1 & B1 returns the same value as=CONCATENATE(A1,B1). In many cases, using the ampersand operator is quicker and simpler than using CONCATENATE to create strings.
Use the TEXT function to combine and format strings.	The TEXT function converts a numeric value to text and combines numbers with text or symbols. For example, if cell A1 contains the number 23.5, you can use the following formula to format the number as a dollar amount: =TEXT(A1,"$0.00") Result: $23.50

 

3) LEFT and RIGHT

LEFT returns the first character or characters in a text string, based on the number of characters you specify.

Cell A2 Sale Price

=LEFT(A2,4) First four characters in the Cel A2

Sale

RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

=RIGHT(A2,5) Last 5 characters of the Cel A2

Price

 

4) TRIM(text)

Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

=TRIM(" First Quarter Earnings ")
Removes leading and trailing spaces from the text in the formula (First Quarter Earnings)

 

5) LEN(text)

LEN returns the number of characters in a text string.

A2 = Phoenix, AZ
=LEN(A2)  Length of the first string   (11)

 

6) Dsum function
The Excel Dsum function calculates the sum of a field (column) in a database for selected records, that satisfy user-specified criteria.

The function is very similar to the Excel Sumifs function, which was first introduced in Excel 2007.

The syntax of the Excel Dsum function is:
DSUM( database, field, criteria )

where the arguments are:
database - A range of cells containing the database. The top row of the database should specify the field names.
field -

The field (column) within the database, that is to be summed.

This can either be a field number, or can be the field name (i.e. the header in the top row of the database) encased in quotes (e.g. "Area", "Quarter", etc).
criteria -

A range of cells that contain the criteria, to specify which records should be included in the calculation.

The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below.
E.g.
Quarter Area
>2 South

The following examples are based on the simple database (below), which stores the sales figures for four sales representatives, working in the North or South area, over the four quarters of a year.

	A	B	C	D
1	Quarter	Area	Sales Rep.	Sales
2	1	North	Jeff	$223,000
3	1	North	Chris	$125,000
4	1	South	Carol	$456,000
5	1	South	Tina	$289,000
6	2	North	Jeff	$322,000
7	2	North	Chris	$340,000
8	2	South	Carol	$198,000
9	2	South	Tina	$222,000
10	3	North	Jeff	$310,000
11	3	North	Chris	$250,000
12	3	South	Carol	$460,000
13	3	South	Tina	$395,000
14	4	North	Jeff	$261,000
15	4	North	Chris	$389,000
16	4	South	Carol	$305,000
17	4	South	Tina	$188,000

Example 1
In the example below, the Dsum function is used to calculate the total sales in quarters 3 & 4, in the "North" area. The criteria are specified in cells F1 - G2 and the Dsum formula is shown in cell F3.
F G
1 Quarter Area
2 >2 North
3 =DSUM( A1:D17, "Sales", F1:G2 )

The above Dsum function calculates the sum of the values in cells D10, D11, D14 & D15, and therefore returns the value $1,210,000.

Example 2
In the example below, the Dsum function is used to calculate the total sales in quarter 3, by sales reps with names beginning with the letter "C". Again, the criteria are specified in cells F1 - G2 and the Dsum formula is shown in cell F3.
F G
1 Quarter Sales Rep.
2 3 C*
3 =DSUM( A1:D17, "Sales", F1:G2 )

The above Dsum function sums the values in cells D11 and D12 and so returns the value $710,000.

Note that, in the above two examples, instead of typing in "Sales" for the field argument, we could have simply used the number 4 (to denote the 4th column of the database).

 

7) Sumifs function
The Excel Sumifs function finds values in one or more supplied arrays, that satisfy a set of criteria, and returns the sum of the corresponding values in a further supplied array.

The function is new in Excel 2007, and so is not available in earlier versions of Excel.

The syntax of the Sumifs function is:
SUMIFS( sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Where the function arguments are:
sum_range - An array of numeric values (or a range of cells containing numbers) which are to be added together if the criteria are satisfied.
criteria_range1
[criteria_range2], ... -

Arrays of values (or ranges of cells containing values) to be tested against the respective criteria1, criteria2, ...
(The supplied criteria_range arrays must all have the same length as the sum_range).
criteria1,
[criteria2], ... - The conditions to be tested against the values in criteria_range1, [criteria_range2], ...
Note that:

The Sumifs function can handle up to 127 pairs of criteria_range and criteria arguments.
Each of the supplied criteria can be either:

a numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2008, TRUE)

or

a text string (e.g. "Name", "Thursday"), which can include wildcards
Wildcards
You can use the following wildcards in text-related criteria:

? - matches any single character
* - matches any sequence of characters

Note that, if you actually want to find the ? or * character, type the ~ symbol before this character in your search.
E.g. the condition "a*e" will match all cells containing a text string beginning with "a" and ending in "e".

or

an expression (e.g. ">12", "<>0").

If your criteria is a text string or an expression, this must be supplied to the Sumifs function in quotes;
The Excel Sumifs function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be equal.

The following examples are based on the simple database above (in the Dsum function)
Example 1

To find the sum of sales in the North area during quarter 1:
=SUMIFS( D2:D13, A2:A13, 1, B2:B13, "North" )

which gives the result $348,000.

In this example, the Excel Sumifs function identifies rows where:

The value in column A is equal to 1

and

The entry in column B is equal to "North"

and calculates the sum of the corresponding values from column D.

I.e. this formula finds the sum of the values $223,000 and $125,000 (from cells D2 and D3).

Example 2

Again, using the data spreadsheet above, we can also use the Sumifs function to find the total sales for "Jeff", during quarters 3 and 4:
=SUMIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

This formula returns the result $571,000.

In this example, the Excel Sumifs function identifies rows where:

The value in column A is greater than 2

and

The entry in column C is equal to "Jeff"

and calculates the sum of the corresponding values in column D.

I.e. this formula finds the sum of the values $310,000 and $261,000 (from cells D8 and D11).