SUBTOTAL

Name

SUBTOTAL(functioncode, value1, value2,...)

Definition

This function calculates a subtotal of a list of numbers using a specified built-in function

Syntax

function code- required and type of subtotal to create.Function code can be a value ranging from 1-11

- 1 is AVERAGE
- 2 is COUNT
- 3 is COUNTA
- 4 is MAX
- 5 is MIN
- 6 is PRODUCT
- 7 is STDEV
- 8 is STDEVP
- 9 is SUM
- 10 is VAR
- 11 is VARP
- Hidden values can be skipped for any of these codes by prepending 10 (to the single-digit codes) or 1 (to the 2-digit codes). e.g. 102 for COUNT while skipping hidden cells, and 110 for VAR while doing so.

value 2- optional and the second number used to compare. Assumed zero by default.
- Cells that are hidden due to auto filter criteria are never included in SUBTOTAL, irrespective of the function_code used.
- Cells within any of the specified range arguments that contain SUBTOTAL calls are ignored to prevent double-counting.

Step 1 :To begin to use the SUBTOTAL formula, start with the Excellentable you would like to edit.

Step 2 : Then type the SUBTOTAL Formula in the cell you have chosen to display the outcome:

Step 3 : Type in the complete SUBTOTAL formula for a cell as shown below:

Step 4 : Excellentable will generate the outcome when hitting enter.

A

B

C

D

1

State

Revenue

Population

Total area in mi2

Land area in mi2

Water area in mi2

House seat(s)

2

Alabama

2.4

4833722

52,420 (135,767)

3

Alaska

242.2413

735132

665,384 (1,723,337)

4

Arizona

23.765

6626624

113,990 (295,233)

5

Arkansas

-56.58

2959373

53,179 (137,733)

6

California

-0.5

38332521

163,695 (423,968)

7

Colorado

4

5268367

104,094 (269,602)

8

Connecticut

1

3596080

5,543 (14,356)

9

Delaware

3.14159

925749

2,489 (6,446)

10

Florida

-3.141

19552860

65,758 (170,312)

11

Georgia

-242.2413

9992167

59,425 (153,910)

12

Hawaii

3141.59

1404054

10,932 (28,314)

13

Idaho

0.5

1612136

83,569 (216,443)

14

Alabama

2.4

4833722

52,420 (135,767)

15

16

259.6812992

	E	F	G
1

2

50,645 (131,170)

1,775 (4,597)

7

3

570,641 (1,477,950)

94,743 (245,383)

1

4

113,594 (294,207)

396 (1,026)

9

5

52,035 (134,770)

1,143 (2,960)

4

6

155,779 (403,466)

7,916 (20,502)

53

7

103,642 (268,432)

452 (1,171)

7

8

4,842 (12,541)

701 (1,816)

5

9

1,949 (5,048)

540 (1,399)

1

10

53,625 (138,888)

12,133 (31,424)

27

11

57,513 (148,958)

1,912 (4,950)

14

12

6,423 (16,635)

4,509 (11,678)

2

13

82,643 (214,044)

926 (2,398)

2

14

50,645 (131,170)

1,775 (4,597)

7

15

16