add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
If you work in Excel, you are a heavy user of formulas that perform calculations such as summing or counting data. Excel provides you with a number of ways to quickly perform simple mathematical calculations, along with complex techniques for adding data according to criteria, by using formulas such as COUNTIF and SUMIF, and array formulas.
Summing Data Easily
To illustrate several techniques for summing numerical data, create a multiplication table in an Excel sheet.
1. In cells A1:A2, enter the digits 1 and 2, respectively.
2. In cells B1:B2, enter the digits 2 and 4, respectively.
3. Select cells A1:B2. Click the Fill handle and copy the cells by dragging down to row 10. Click the Fill handle again drag and across to column J.
4. Select the multiplication table (current region) by selecting cell A1 and pressing Ctrl+*.
5. Right-click the Status bar. From the shortcut menu, select Sum.
The result: As displayed in the Status bar, the resulting total is 3025.
Summing values in a vertical range
Select the multiplication table, and press Alt+= or click the AutoSum icon (sigma) in the Standard toolbar.
The result: The SUM formula is displayed automatically at the bottom of each column.
Summing values in vertical and horizontal ranges
Delete the row displaying the totals you just created in Adding values in a vertical range (click Ctrl+Z to Undo). Select the range of data, including the empty rows and columns surrounding the data A1:K11.
Tip – A quick technique for selecting data without using the mouse: select cell A1, press Shift, and select cell K11.
Now press Alt+= or click the AutoSum icon.
Result: The SUM formula is added to the end of each row and the bottom of each column.
Summing data by selecting cells
Delete the horizontal and vertical totals by pressing Ctrl+Z or clicking the Undo icon.
To sum the data in cells G1 through J1: select the cells G1:K1 (the selection includes an empty cell), and press Alt+=. The resulting total is displayed in Cell K1.
Sum subtotals
Delete the total you created by clicking Undo.
1. Right-click Row 5. From the shortcut menu, select Insert.
2. Select Cell A1, select the current region, press Ctrl+*, and press Alt+= or click the AutoSum icon.
3. Select the range A6:J11, and press Alt+= or click AutoSum.
Result
Row 5 contains the formulas for summing the data in Rows 1:4.
Row 12 contains the formulas for summing the data in Rows 6:11.
Select Cell A1, select the current region, press Ctrl+*, and press Alt+= or click the AutoSum icon.
Notice the formula in Cell A13 is =SUM(A12,A5). Excel recognized the subtotals in Rows 5 and 12 and “understood” that these are indeed subtotals.
Note
Do not leave empty spaces between rows. Excel handles them as borders and creates formulas for summing the data only up to those rows.
Extending the range of sums for the SUM formula
In Cell E15, insert the SUM formula with the total for the data range A1:C5, =SUM(A1:C5). Now, say that we want to extend the range of cells included in the total so that it includes Cell C10.
The technique: Select Cell E1, and press F2 (to edit the cell). Alternatively, select the formula in the Formula bar. Notice the cell references in the formula.
Click the handle of the range. The handle is located in the bottom right corner of the range, Cell C5. Drag the handle to extend the range. Press Enter after you have made your selection.
AutoSum, additional functions
New
In 2002
Excel 2002 has a new icon that offers new AutoSum options. Click the small arrow
to the right of the AutoSum icon to select another function for calculating
sums.
To insert the AutoSum icon, right-click one of the toolbars. From the shortcut menu, select Customize. Select the Commands tab, and from the Insert category, click and drag the AutoSum icon onto the toolbar.
Adding, subtracting, multiplying and dividing without using formulas
In the Operation area of the Paste Special dialog box, you can perform the four mathematical operations without using formulas. For example, a data range has data in Columns E and F.
1. Copy the cell range E1:E13.
2. Select the range F1:F13.
3. Right-click, and from the shortcut menu, select Paste Special.
4. In Operation, select Add,and click OK.
Tip – Multiplying by -1
Use Paste Special to multiply a range of numbers by -1.
1. In any cell, type -1.
2. Select the cell and press Ctrl+C (copy).
3. Select the range to be multiplied by -1.
4. Right-click, and from the shortcut menu, select Paste Special.
5. Select the Multiply option.
6. Click OK.
If one of the cells contains a formula, *-1 and parentheses are added to the formula.
Tip – Use the Formula Bar to perform quick calculations without a formula
In the Formula Bar, type the = sign, and then multiply two numbers (for example, 8*5). Press F9, then press Enter.
Summing data in the intersection of two ranges
Cells A1:D10 and Cells A5:E7 contain data. The two areas include some shared cells. Cells A5:D7 are shared.
Sum the data in only the shared area by using the SUM formula.
The formula is =SUM(A1:D10 A5:E7).
The technique is to leave a space between the two ranges.
Summing data from a cell in a number of sheets
Sum a single cell from a number of sheets.
For example: A workbook has four successive sheets. The names of the sheets are January, February, March and April. The formula: =SUM(January:April!B2).
1. Select the sheet in which you want to enter the formula.
2. Type =SUM(.
3. Select the tab for the first sheet, January.
4. Press Shift, and select the tab for the last sheet, April.
5. Select Cell B2.
6. Press Enter.
Summing According to Criteria, the SUMIF Formula
The SUMIF formula is one of the best and most useful formulas in Excel. The formula allows you to sum data according to various criteria. There is a wide range of criteria available to the SUMIF formula. In the figure below, notice that the total has been calculated according to two different types of criteria: one is textual, and the other is numeric. The main advantage to the SUMIF formula is that you can sum data in an unsorted range.
The SUMIF formula, arguments
The first argument, Range
Select the range for the criterion.
The second argument, Criteria
Type the criterion (in quotes), or select the cell with the criterion.
The third argument, Sum_Range
Select the range that contains the data to be totaled.
The formula in Cell H4 is =SUMIF(B:B,G4,D:D)
Note
The cell ranges for the first and third arguments must be the same size.
Using two arguments of the SumIf formula
The third argument, Sum_Range, is colored gray in the argument box. This indicates that it is optional to use this argument. This means that you can use the SumIf formula with only the first two arguments.
In other words, sum the data range that you selected in the first argument according to the criteria in the second argument.
Using comparison operators (< >) as criteria
Use the < or > comparison operators as criteria to sum data.
For example:
In Cell G4 (in the figure), type >100. The result is a total of the amounts in Column D of invoice numbers that are greater than 100. You can enter a criterion into a formula (not recommended). If you choose to do so, be sure to place quotation marks before and after the criterion.
The syntax of the formula with criteria in the second argument is =SUMIF(C:C,”>100”,D:D).
Using SUMIF to sum two ranges according to criteria
The SUMIF formula sums data from a single range only. If you want to use the SUMIF formula to sum or subtract data from different ranges, then you have to create two SUMIF formulas and combine them into a single formula.
The combined formula is =SUMIF(B:B,G4,D:D)-SUMIF(B:B,G4,E:E).
In two cells, enter two SUMIF formulas.
1. In the formula bar, select the formula that is to be copied, without the = sign.
2. Press Ctrl+C (Copy).
3. Click Cancel (the X sign to the left of the formula in the formula bar).
4. Select the second formula in the formula bar, and at the end of the formula, add a – sign (or + sign).
5. Press Ctrl+V.
6. Press Enter.
Summing totals with text characters as criteria
With the SUMIF formula, you can find totals according to complex criteria, such as the characters at the beginning of text, in the center of text, and/or special symbols.
In the figure below, notice the wide range of possibilities available for finding totals according to complex criteria.
The COUNT Formula
The COUNT formula comes in several forms. In the figure below, notice the various uses of the COUNT function.
In the Database category of Paste Function, there are two additional versions of the COUNT formula, the DCOUNT formula and the DCOUNTA formula. See Chapter 15, Data.
Counting the number of cells in a range that includes numbers
Example:
A data range that is 10 lines by 10 columns includes 100 cells with numeric data.
The formula is =COUNT(Table) (the Name Table was defined for the table of data).
Counting the number of cells in a range that includes text
Add a header row with text to the previous example.
The formula is =COUNTA(Table)-COUNT(Table). The COUNTA formula returns the number of cells in a range that includes any type of data. The COUNT formula returns the number of cells that include only numeric data.
Counting the cells with data according to criteria
The data sheet in the figure below includes Names that have been defined for the columns, according to the column headers.
To create Names according to headers, select a cell in the data table, press Ctrl+*, and press Ctrl+Shift+F3. Select Top Row, and click OK.
The AND formula
Count the number of cells in the column Customer Name = Intel with the criterion USA in the Market column. The result of the calculation is 2. The formula is ={SUM((Market=”USA”)*(Customer_Name=”Intel”))}.
The * symbol in the array formula returns a result equal to the AND formula. Here is a shortcut for inserting an array formula after entering the formula into the cell: select the cell, press F2 (Edit), and press Ctrl+Shift+Enter.
The OR formula
Replace the * sign with the + sign in the formula. The result of the calculation is 18, five cells in the column named Customer Name, and thirteen cells in the column named Market. The + sign in the array formula returns a result equal to the OR formula.
You can use the COUNTIF formula instead of the array formula:
=COUNTIF (Market, “USA”)+COUNTIF(Customer_Name, “Intel”).
Comparing and Merging Lists, the COUNTIF Formula
The ability to compare two lists helps you to pinpoint the similarities and the differences between the lists. You can identify a name or item that appears in one list and not the other, and names or items that appear in both lists.
See more about comparing lists in Chapter 21, Consolidating Data.
A sample list: employees, inventory items, clients, suppliers and/or list of account names in the accounting system, and more. In the example, compare two lists of employees. In the figure below, notice that Column A contains one list of employees and Column E contains the other.
Stage 1 – check each list
In Cell B2, enter the formula =IF(COUNTIF(E:E,A2)>0,3,1).
In Cell F2, enter the formula =IF(COUNTIF(A:A,E2)>0,3,2).
Explanation
The COUNTIF formula returns the total number of times that a particular criterion appears in a range of cells or a column. When the result of the calculation is 3, the name of the employee appears in both lists. When the result of the calculation is 1, the name of the employee appears in the first list. When the result of the calculation is 2, the name of the employee appears only in the second list.
Stage 2 – merging the lists
Select Cell B2, and click the Sort Ascending icon on the standard toolbar.
Select Cell F2, and click the Sort Ascending icon on the standard toolbar.
In Column E, beginning with Cell E2, copy the names of those employees for whom the digit 2 appears in Column F. Paste the names at the bottom of the first list.
In the figure below, the second list of employees now includes only those employees whose names do not appear in the first list.