Use the SUMIF spreadsheet function to create a sum for one range that's based on a value in another range. For example, for every cell in the range B5:B25 that contains the value "Northwind", the following formula calculates the sum for the corresponding cells in the range F5:F25.

=SUMIF(B5:B25,"Northwind",F5:F25)

The COUNTIF spreadsheet function counts the occurrences of a value in a range of cells — for example, the number of cells in the range B5:B25 that contain the text "Northwind".

=COUNTIF(B5:B25,"Northwind")

You can create a checkbook register in a spreadsheet to track your banking transactions. As part of the spreadsheet, you can build a formula to calculate your running balance. In this example, assume that cell F6 contains the previous balance, cell D7 contains the first transaction's deposit subtotal, and cell E7 contains any cash-received amount.

To calculate the current balance for the first transaction, enter the following formula in cell F7:

=SUM(F6,D7,–E7)

As you enter new transactions, copy this formula into the current balance cell for each new deposit.

By using the ampersand (&) text operator, you can join, or concatenate, a list of first names in one column with a list of last names in another column by using a formula. In these examples, assume that cell D5 contains the first name and cell E5 contains the last name.

To display the full name in the format "first_name last_name" (for example, "John Smith"):

=D5&" "&E5

To display the full name in the format "last_name, first_name" (for example, "Smith, John"):

=E5&", "&D5

To join, or concatenate, two values to produce one continuous text value, use the ampersand (&) text operator. To connect a text string with a number, date, or time value stored in a cell, use the TEXT spreadsheet function. For example, if cell G5 contains a billing date of 5-Jun-2000, you can display the text "Statement date: 5-Jun-2000" by using this formula:

="Statement date: "&TEXT(G5, "d-mmm-yyyy")

You can increase a numeric value that's stored in a cell by a percentage. In this example, assume that cell G5 contains the value that you want to increase by 5 percent. Multiply the value in G5 by 1 to return the starting value, and then add the percent you want to increase it by so that the formula returns the increased value as a result.

=G5*(1+5%)

If the percentage amount is stored in cell G2, use this formula:

=G5*(1+$G$2)

The reference to cell G2 is an absolute cell reference, so the formula can be copied to other cells without changing the reference to G2.