Docstoc

Subtotal-Examples

Document Sample
Subtotal-Examples Powered By Docstoc
					Subtotal Function
Returns a subtotal in a list or database. Subtotals can be insert automatically into sorted data using the Subtotal command in the Outline group on the Data tab in Excel 2007 and from the Data Menu in earlier versions of Excel. Syntax: SUBTOTAL(function num, range or values) Function num is the number 1 to 11 (includes hidden values) or 101 to 111 (ignores hidden values) that specifies which function to use in calculating subtotals within a list. Function numbers Includes hidden values 1 2 3 4 5 6 7 8 9 10 11

Ignores hidden values 101 102 103 104 105 106 107 108 109 110 111

Function AVERAGE COUNT COUNTA MAX MIN PRODUCT STDEV STDEVP SUM VAR VARP

If there are other subtotals within the subtotal range, the subtotal values will be ignored. The Subtotal function ignores rows not included in filter results, regardless of the function number. To insert subtotals, first sort the data with a primary sort on the field you wish to create subtotals for. If you have a Department field and want to see totals by Department, then first sort on the Department field. Go to the Detail Worksheet to see information about the subtotals you see in this workbook. Go to the Use Dialog sheet to see information on how to use the Insert Subtotal dialog box. Boni Mays Fayetteville Technical Community College

1

www.maysstuff.com

Inserting Subtotals
The subtotal dialog box is shown to the right. The first option allows you to choose the field on which you wish to base your subtotal. At each change in: means that every time there is a change in the data, a subtotal will be inserted. Make sure you sort before subtotaling. Your primary sort and At each change in: choice should be the same column heading. The second option allows you to choose the function you wish subtotal to insert. The Add subtotal to: section allows you to check the fields to which you wish to apply that subtotal. By default, Replace current subtotals and Summary below data will be checked. Remove the checkmark from Replace current data to add a second subtotal to data that already contains a subtotal. Remove the check from Summary below data to have the Summary appear above data. Check Page break between groups to have each subtotal section print on a different page. When you have finished working with subtotals and wish to use other tools with your data, open the subtotal dialog box and click Remove all to remove all subtotals and inserted rows.

Boni Mays Fayetteville Technical Community College

2

www.maysstuff.com

In this Workbook
Outline Bar
The image to the right is a picture of the outline bar that appears when subtotals are inserted into a worksheet. The numbers across the top allow you to choose a level of visibility for your subtotals. Click the 3 to see all of your subtotals and record detail. Click the 2 to collapse your subtotals to display only the subtotals and grand total. Click the 1 to display only the Grand total. Adding additional subtotals will add levels to the outline. The plus and minus signs on the bar are individual collapse and expand buttons for the detail sections of your outline. The worksheets in this workbook are showing the detail for only one section, with the rest collapsed to level 2.

Worksheet
Data By type

Information About the Sheet
This worksheet contains the data used on each sheet in its original format, sorted by part number. In this worksheet, the data is sorted by Type and then by Total Value, with the largest values listed first. The subtotal is summing the Quantity and Total Value columns. Click cell F18 to see the subtotal function: =SUBTOTAL(9,F2:F17). The 9 indicates a function type of SUM. In this worksheet, the data is sorted by Dept and then by Total Value, with the largest values first. The subtotal is summing the Quantity and Total Value columns. Click cell F20 to see the subtotal function: =SUBTOTAL(9,F2:F19). The 9 indicates a function type of SUM. In this worksheet, the data is sorted by Dept and then by Total Value, with the largest values first. The subtotal is summing the Quantity and Total Value columns. Click cell F20 to see the subtotal function: =SUBTOTAL(1,F2:F19). The 1 indicates a function type of AVERAGE. In this worksheet, the data is sorted by Dept and then by Total Value, largest values first. The first subtotal is averaging the Cost and a second subtotal has been added to Count Department. Click cell C20 to see the subtotal function: =SUBTOTAL(3,C2:C19). The 3 indicates a function type of COUNT. In this worksheet a subtotal has been applied to a table that was not sorted to match. Too many subtotals and repeated labels for subtotals indicate this kind of a problem. Remove the subtotals, sort, and try again. In this worksheet the data is sorted by Type, then by Dept, and then by Part Number. It has been filtered to display only Dept 1. Click cell F18 to see the subtotal function: =SUBTOTAL(9,F2:F17). You can see that the subtotal range includes rows hidden by the filter, but that the total displays the total of only the two visible cells. In this worksheet, the Subtotal function in cell F18 has been edited to use the ignore hidden values, sum function number: =SUBTOTAL(109,F2:F17). Rows 3 through 16 have been hidden and, as you can see, those values are ignored by the subtotal function. In cell F30, the function has not been changed. Rows 20 through 28 have been hidden, but their values are still included in the F30 subtotal. 3 www.maysstuff.com

By dept

Avg by Dept

Avg Cost and Cnt by Dept

Wrong

With Filter

With Hidden Rows

Boni Mays Fayetteville Technical Community College

Part Number Type 11164539 RT 11164540 AC 11164541 AB 11164542 RT 11164544 AC 11164545 DE 11164546 DE 11164547 DE 11164548 DB 11164549 DE 11164550 DB 11164551 RT 11164556 DE 11164557 AB 11164558 DE 11164559 AC 11164560 RT 11164561 RT 11164562 DE 11164563 DE 11164564 AC 11164565 RT 11164567 AB 11164568 AC 11164569 DE 11164570 DB 11164571 DE 11164572 AB 11164574 RT 11164575 AB 11164579 AC 11164580 DE 11164581 RT 11164582 AB 11164583 DE 11164584 DB 11164585 DB 11164586 RT 11164587 RT 11164591 AC 11164592 DE 11164593 DE 11164594 AC 11164595 RT 11164596 RT

Department Dept 2 Dept 5 Dept 4 Dept 2 Dept 5 Dept 5 Dept 2 Dept 4 Dept 5 Dept 4 Dept 1 Dept 2 Dept 4 Dept 2 Dept 1 Dept 1 Dept 5 Dept 3 Dept 2 Dept 1 Dept 5 Dept 2 Dept 3 Dept 1 Dept 3 Dept 3 Dept 5 Dept 4 Dept 1 Dept 5 Dept 4 Dept 4 Dept 4 Dept 1 Dept 2 Dept 4 Dept 3 Dept 3 Dept 1 Dept 3 Dept 1 Dept 5 Dept 4 Dept 4 Dept 2

Cost 55.30 69.58 47.87 16.22 54.36 74.45 52.03 25.74 39.12 10.97 18.56 45.80 88.39 79.08 15.28 2.77 40.96 25.07 0.84 9.42 16.02 77.83 1.54 9.81 1.77 7.99 23.54 71.10 64.64 50.87 27.60 46.85 1.18 35.00 12.13 51.16 27.88 2.31 1.96 0.92 17.87 10.90 81.80 3.64 23.70

Quantity 15 7 1 5 18 13 11 8 14 3 12 17 2 12 7 15 0 12 12 3 7 4 15 1 8 13 10 4 5 19 3 9 16 10 13 5 2 16 5 4 1 4 11 6 0

Total Value 829.50 487.06 47.87 81.10 978.48 967.85 572.33 205.92 547.68 32.91 222.72 778.60 176.78 948.96 106.96 41.55 300.84 10.08 28.26 112.14 311.32 23.10 9.81 14.16 103.87 235.40 284.40 323.20 966.53 82.80 421.65 18.88 350.00 157.69 255.80 55.76 36.96 9.80 3.68 17.87 43.60 899.80 21.84 -

Boni Mays Fayetteville Technical Community College

4

www.maysstuff.com

11164597 11164598 11164600 11164601 11164602 11164603 11164604 11164611 11164612 11164613 11164614 11164615 11164616 11164617 11164618 11164619 11164620 11164621 11164622 11164623 11164624 11164625 11164626 11164627 11164628 11164629 11164630 11164631 11164632 11164633 11164634 11164635 11164636 11164637

AB DB AC RT AB DE DB DE DE AB AB DE AC RT AB RT DE DE RT DB AB RT AB AB AC DE DE DB DE AB DE RT DE AB

Dept 4 Dept 1 Dept 1 Dept 1 Dept 4 Dept 3 Dept 1 Dept 2 Dept 2 Dept 5 Dept 4 Dept 4 Dept 2 Dept 2 Dept 5 Dept 1 Dept 3 Dept 5 Dept 2 Dept 1 Dept 2 Dept 2 Dept 1 Dept 5 Dept 2 Dept 5 Dept 4 Dept 1 Dept 1 Dept 2 Dept 4 Dept 2 Dept 3 Dept 5

3.22 8.74 5.72 60.24 8.96 60.72 1.11 25.54 14.46 93.15 91.37 43.04 1.66 25.92 4.33 12.53 91.76 33.67 12.75 21.08 27.14 84.92 0.81 3.06 19.10 76.98 56.25 19.41 64.04 46.39 62.49 53.66 1.71 50.87

2 4 0 12 17 16 4 5 1 9 8 14 18 17 1 8 9 5 6 12 4 4 19 0 14 6 8 6 14 16 0 2 2 2

6.44 34.96 722.88 152.32 971.52 4.44 127.70 14.46 838.35 730.96 602.56 29.88 440.64 4.33 100.24 825.84 168.35 76.50 252.96 108.56 339.68 15.39 267.40 461.88 450.00 116.46 896.56 742.24 107.32 3.42 101.74

Boni Mays Fayetteville Technical Community College

5

www.maysstuff.com

Part Number Type 11164575 AB 11164557 AB 11164613 AB 11164633 AB 11164614 AB 11164582 AB 11164572 AB 11164602 AB 11164624 AB 11164637 AB 11164541 AB 11164567 AB 11164626 AB 11164597 AB 11164618 AB 11164627 AB AB Total AC Total DB Total DE Total RT Total Grand Total

Department Dept 5 Dept 2 Dept 5 Dept 2 Dept 4 Dept 1 Dept 4 Dept 4 Dept 2 Dept 5 Dept 4 Dept 3 Dept 1 Dept 4 Dept 5 Dept 5

Cost 50.87 79.08 93.15 46.39 91.37 35.00 71.10 8.96 27.14 50.87 47.87 1.54 0.81 3.22 4.33 3.06

Quantity 19 12 9 16 8 10 4 17 4 2 1 15 19 2 1 0 139 98 72 184 150 643

Total Value 966.53 948.96 838.35 742.24 730.96 350.00 284.40 152.32 108.56 101.74 47.87 23.10 15.39 6.44 4.33 5,321.19 2,912.60 1,594.65 7,513.75 4,499.30 21,841.49

Boni Mays Fayetteville Technical Community College

6

www.maysstuff.com

Part Number 11164557 11164539 11164551 11164633 11164546 11164617 11164625 11164565 11164628 11164583 11164611 11164624 11164635 11164542 11164622 11164616 11164612 11164562 11164596

Type AB RT RT AB DE RT RT RT AC DE DE AB RT RT RT AC DE DE RT

Department Dept 1 Total Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Dept 2 Total Dept 3 Total Dept 4 Total Dept 5 Total Grand Total

Cost 79.08 55.30 45.80 46.39 52.03 25.92 84.92 77.83 19.10 12.13 25.54 27.14 53.66 16.22 12.75 1.66 14.46 0.84 23.70

Quantity 138 12 15 17 16 11 17 4 4 14 13 5 4 2 5 6 18 1 12 0 176 97 117 115 643

Total Value 3,254.06 948.96 829.50 778.60 742.24 572.33 440.64 339.68 311.32 267.40 157.69 127.70 108.56 107.32 81.10 76.50 29.88 14.46 10.08 5,943.96 2,339.15 4,390.93 5,913.39 21,841.49

Boni Mays Fayetteville Technical Community College

7

www.maysstuff.com

Part Number Type 11164632 DE 11164601 RT 11164582 AB 11164574 RT 11164623 DB 11164550 DB 11164631 DB 11164558 DE 11164619 RT 11164559 AC 11164598 DB 11164563 DE 11164592 DE 11164626 AB 11164568 AC 11164587 RT 11164604 DB 11164600 AC

Department Cost Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Average Dept 2 Average Dept 3 Average Dept 4 Average Dept 5 Average Grand Average

Quantity 64.04 60.24 35.00 64.64 21.08 18.56 19.41 15.28 12.53 2.77 8.74 9.42 17.87 0.81 9.81 1.96 1.11 5.72 14 12 10 5 12 12 6 7 8 15 4 3 1 19 1 5 4 0 8 9 10 7 8 8

Total Value 896.56 722.88 350.00 323.20 252.96 222.72 116.46 106.96 100.24 41.55 34.96 28.26 17.87 15.39 9.81 9.80 4.44 180.78 312.84 233.92 258.29 394.23 276.47

Boni Mays Fayetteville Technical Community College

8

www.maysstuff.com

Part Number Type 11164632 DE 11164601 RT 11164582 AB 11164574 RT 11164623 DB 11164550 DB 11164631 DB 11164558 DE 11164619 RT 11164559 AC 11164598 DB 11164563 DE 11164592 DE 11164626 AB 11164568 AC 11164587 RT 11164604 DB 11164600 AC Dept 1 Count Dept 2 Count Dept 3 Count Dept 4 Count Dept 5 Count Grand Count

Department Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 18 Dept 1 Average 19 Dept 2 Average 10 Dept 3 Average 17 Dept 4 Average 15 Dept 5 Average 83 Grand Average

Cost 64.04 60.24 35.00 64.64 21.08 18.56 19.41 15.28 12.53 2.77 8.74 9.42 17.87 0.81 9.81 1.96 1.11 5.72 20.50 35.50 22.17 42.45 42.79 33.27

Quantity 14 12 10 5 12 12 6 7 8 15 4 3 1 19 1 5 4 0

Total Value 896.56 722.88 350.00 323.20 252.96 222.72 116.46 106.96 100.24 41.55 34.96 28.26 17.87 15.39 9.81 9.80 4.44 -

Boni Mays Fayetteville Technical Community College

9

www.maysstuff.com

Part Number

Type DB Total DE Total AC Total DE Total AC Total RT Total AB Total RT Total DE Total DB Total AC Total RT Total DB Total RT Total DB Total AB Total DB Total 11164632 DE DE Total 11164539 RT 11164542 RT RT Total DE Total RT Total AB Total DE Total RT Total DE Total RT Total DE Total AC Total RT Total AB Total RT Total AC Total AB Total RT Total AB Total DE Total DB Total RT Total AC Total DE Total AB Total DE Total

Department

Cost

Quantity 12 7 15 3 1 5 10 5 1 4 0 12 4 8 12 19 6 14 14 15 5 20 11 17 12 12 4 13 0 6 18 23 4 4 14 16 14 15 8 15 16 4 27 1 13

Dept 1 Dept 2 Dept 2

64.04 55.30 16.22

Total Value 222.72 106.96 41.55 28.26 9.81 323.20 350.00 9.80 17.87 34.96 722.88 4.44 100.24 252.96 15.39 116.46 896.56 896.56 829.50 81.10 910.60 572.33 778.60 948.96 10.08 311.32 157.69 142.16 29.88 517.14 108.56 339.68 267.40 742.24 408.16 23.10 14.16 159.63 36.96 3.68 1,800.78 47.87 415.61

Boni Mays Fayetteville Technical Community College

10

www.maysstuff.com

AB Total AC Total DE Total RT Total DB Total AC Total RT Total AB Total DE Total AC Total DE Total DB Total RT Total AC Total DE Total AB Total DE Total AB Total DE Total AB Total DE Total AB Total Grand Total

4 3 9 16 5 11 6 27 22 25 13 14 0 7 10 19 4 10 5 0 6 2 643

284.40 82.80 421.65 18.88 255.80 899.80 21.84 889.72 1,052.56 1,465.54 967.85 547.68 112.14 235.40 966.53 43.60 842.68 168.35 461.88 101.74 21,841.49

Boni Mays Fayetteville Technical Community College

11

www.maysstuff.com

Part Number Type 11164582 AB 11164626 AB AB Total 11164559 AC 11164568 AC 11164600 AC AC Total 11164550 DB 11164598 DB 11164604 DB 11164623 DB 11164631 DB DB Total 11164558 DE 11164563 DE 11164592 DE 11164632 DE DE Total 11164574 RT 11164587 RT 11164601 RT 11164619 RT RT Total Grand Total

Department Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1 Dept 1

Cost 35.00 0.81 2.77 9.81 5.72 18.56 8.74 1.11 21.08 19.41 15.28 9.42 17.87 64.04 64.64 1.96 60.24 12.53

Quantity 10 19 29 15 1 0 16 12 4 4 12 6 38 7 3 1 14 25 5 5 12 8 30 138

Total Value 350.00 15.39 365.39 41.55 9.81 51.36 222.72 34.96 4.44 252.96 116.46 631.54 106.96 28.26 17.87 896.56 1,049.65 323.20 9.80 722.88 100.24 1,156.12 3,254.06

Boni Mays Fayetteville Technical Community College

12

www.maysstuff.com

Part Number Type 11164582 AB 11164637 AB AB Total 11164559 AC 11164564 AC AC Total DB Total DE Total RT Total Grand Total

Department Dept 1 Dept 5 Dept 1 Dept 5

Cost 35.00 50.87 2.77 16.02

Quantity 10 2 12 15 7 98 0 0 0 34

Total Value 350.00 101.74 451.74 41.55 112.14 2,912.60 605.43

Boni Mays Fayetteville Technical Community College

13

www.maysstuff.com

More information and examples are also available for the following functions, visit Boni's Site: http://www.maysstuff.com/excel/ AVERAGE AVERAGEIF AVERAGEIFS CEILING COUNT COUNTA COUNTIF COUNTIFS DATEDIF DAY EDATE EOMONTH EVEN FIND FLOOR HLOOKUP HOUR IF IFERROR INT LEFT LEN MAX MID MIN MINUTE MONTH MROUND NETWORKDAYS NOW ODD PMT RIGHT ROUND ROUNDDOWN ROUNDUP SECOND SUBTOTAL SUM SUMIF SUMIFS SUMPRODUCT TIME TIMEVALUE TODAY TRUNC VLOOKUP WORKDAY YEAR


				
DOCUMENT INFO
Shared By:
Tags: Subto, tal-E
Stats:
views:61
posted:11/28/2009
language:English
pages:14
Description: Subtotal-Examples