1
00:00:00,080 --> 00:00:04,661
Here is the trap: a six-order report shows five hundred forty dollars.

2
00:00:04,661 --> 00:00:09,594
You filter it to East, but the SUM cell still says five hundred forty.

3
00:00:09,594 --> 00:00:12,412
The visible East orders actually total four hundred.

4
00:00:12,412 --> 00:00:16,288
In this worked lesson, we will build both formulas, switch the

5
00:00:16,288 --> 00:00:19,459
filter twice, and then test one manually hidden row.

6
00:00:19,459 --> 00:00:22,631
The answer file lets you check every number independently.

7
00:00:23,231 --> 00:00:26,274
Open the practice Excel workbook in Google Sheets.

8
00:00:26,274 --> 00:00:30,460
It preserves three columns and numeric amounts across regional import settings.

9
00:00:30,460 --> 00:00:35,786
East orders are one hundred twenty, forty-five, two hundred, and thirty-five dollars.

10
00:00:35,786 --> 00:00:38,450
West orders are eighty and sixty dollars.

11
00:00:38,450 --> 00:00:41,113
Read those six values before adding formulas.

12
00:00:41,113 --> 00:00:45,298
Put calculation cells above or beside the data rather than inside

13
00:00:45,298 --> 00:00:48,723
the amount range, so the total never includes itself.

14
00:00:49,323 --> 00:00:53,342
In an empty cell outside the table, enter equals SUM, open

15
00:00:53,342 --> 00:00:56,265
parenthesis, C two colon C seven, close parenthesis.

16
00:00:56,265 --> 00:00:58,091
It returns five hundred forty.

17
00:00:58,091 --> 00:01:01,014
That is the baseline across all six amounts.

18
00:01:01,014 --> 00:01:05,033
If you get a different value, inspect the imported amount cells:

19
00:01:05,033 --> 00:01:08,687
they must be numbers, not dollar signs embedded in text.

20
00:01:08,687 --> 00:01:13,437
The CSV uses plain numbers so the sheet can calculate them without cleaning.

21
00:01:14,037 --> 00:01:19,012
Select the header and all six data rows, then use Data, Create a filter.

22
00:01:19,012 --> 00:01:21,855
In the Region header, leave only East selected.

23
00:01:21,855 --> 00:01:25,764
You should now see four order rows and two West rows

24
00:01:25,764 --> 00:01:27,185
hidden by the filter.

25
00:01:27,185 --> 00:01:31,094
The SUM baseline still includes all six values, so it stays

26
00:01:31,094 --> 00:01:32,516
at five hundred forty.

27
00:01:32,516 --> 00:01:37,136
This mismatch is the symptom we are fixing, not a spreadsheet calculation failure.

28
00:01:37,136 --> 00:01:40,689
Select only A one through C seven for the filter.

29
00:01:40,689 --> 00:01:44,598
Keep every summary cell outside that selection, or the filter may

30
00:01:44,598 --> 00:01:48,508
hide the answer at the same time as a data row.

31
00:01:48,508 --> 00:01:52,772
Read the four visible East order IDs before trusting the displayed total.

32
00:01:53,372 --> 00:01:57,484
In the next empty cell, type exactly equals SUBTOTAL, open parenthesis,

33
00:01:57,484 --> 00:02:00,849
nine, comma, C two colon C seven, close parenthesis.

34
00:02:00,849 --> 00:02:02,718
Function code nine means SUM.

35
00:02:02,718 --> 00:02:07,578
In Google Sheets, rows hidden by an automatic filter are excluded from SUBTOTAL.

36
00:02:07,578 --> 00:02:10,569
With East selected, this formula returns four hundred.

37
00:02:10,569 --> 00:02:14,681
If you see five hundred forty, confirm that an actual sheet

38
00:02:14,681 --> 00:02:19,915
filter is active and that the formula points to C two through C seven.

39
00:02:19,915 --> 00:02:24,027
Put this formula in an empty cell such as E three,

40
00:02:24,027 --> 00:02:27,017
not below the table inside the amount column.

41
00:02:27,017 --> 00:02:30,008
The fixed range covers this six-row exercise.

42
00:02:30,008 --> 00:02:34,120
If you later append orders, extend the range and keep the

43
00:02:34,120 --> 00:02:36,737
subtotal cell out of its own calculation.

44
00:02:37,337 --> 00:02:41,142
Read the four visible East IDs, not just the formula result:

45
00:02:41,142 --> 00:02:46,675
H one-oh-one is one hundred twenty; H one-oh-three is forty-five; H

46
00:02:46,675 --> 00:02:51,171
one-oh-four is two hundred; H one-oh-six is thirty-five.

47
00:02:51,171 --> 00:02:52,901
Adding them produces four hundred.

48
00:02:52,901 --> 00:02:56,705
This row-by-row reconciliation catches a wrong filter, an accidentally

49
00:02:56,705 --> 00:02:59,126
included blank row, or a changed amount.

50
00:02:59,126 --> 00:03:02,585
The guide provides the exact fictional answer list for comparison.

51
00:03:03,185 --> 00:03:06,397
Open the Region filter again and select West only.

52
00:03:06,397 --> 00:03:11,037
Now the visible rows are H one-oh-two for eighty dollars and

53
00:03:11,037 --> 00:03:13,179
H one-oh-five for sixty.

54
00:03:13,179 --> 00:03:18,176
The SUBTOTAL formula changes to one hundred forty, while SUM remains five hundred forty.

55
00:03:18,176 --> 00:03:22,102
This second filter proves the formula responds to visibility rather than

56
00:03:22,102 --> 00:03:24,244
being a typed-in East total.

57
00:03:24,244 --> 00:03:27,813
Clear the filter afterward to return to all six rows.

58
00:03:28,413 --> 00:03:29,874
One more case matters.

59
00:03:29,874 --> 00:03:33,891
Code nine always ignores rows hidden by an automatic filter, but

60
00:03:33,891 --> 00:03:36,447
it may include a row hidden manually.

61
00:03:36,447 --> 00:03:39,734
Code one-oh-nine also excludes manually hidden rows.

62
00:03:39,734 --> 00:03:44,481
Restore the East filter, hide the H one-oh-three row manually, and

63
00:03:44,481 --> 00:03:45,942
compare the two formulas.

64
00:03:45,942 --> 00:03:49,594
The first remains four hundred; the second becomes three hundred

65
00:03:49,594 --> 00:03:52,150
fifty-five, because forty-five is removed.

66
00:03:52,150 --> 00:03:55,802
This is a separate visibility rule, not a different aggregation.

67
00:03:55,802 --> 00:04:00,915
Use the row header's Hide row action for the forty-five-dollar order

68
00:04:00,915 --> 00:04:03,106
while the East filter remains selected.

69
00:04:03,106 --> 00:04:07,123
Do not remove East from the filter or change the region

70
00:04:07,123 --> 00:04:11,140
criteria; the point is to compare automatic filtering with an additional

71
00:04:11,140 --> 00:04:14,061
manual hide on the same four matching records.

72
00:04:14,661 --> 00:04:18,492
Use an independent second formula to check that the sum matches

73
00:04:18,492 --> 00:04:21,625
the number of rows you think you are viewing.

74
00:04:21,625 --> 00:04:25,107
In an empty summary cell, enter equals SUBTOTAL, open parenthesis,

75
00:04:25,107 --> 00:04:28,938
one-oh-three, comma, A two colon A seven, close parenthesis.

76
00:04:28,938 --> 00:04:34,161
Code one-oh-three counts nonblank visible order IDs and ignores manually hidden rows too.

77
00:04:34,161 --> 00:04:37,991
East shows four before the manual hide, West shows two, and

78
00:04:37,991 --> 00:04:41,473
East shows three after you hide H one-oh-three.

79
00:04:41,473 --> 00:04:45,303
A count mismatch means you should inspect the filter or hidden

80
00:04:45,303 --> 00:04:47,392
rows before trusting the dollar total.

81
00:04:47,392 --> 00:04:51,222
Every fictional order ID is filled, so COUNTA serves as a

82
00:04:51,222 --> 00:04:52,615
reliable row count here.

83
00:04:52,615 --> 00:04:56,446
If your real list permits blank IDs, use a different column

84
00:04:56,446 --> 00:04:57,838
that is guaranteed populated.

85
00:04:57,838 --> 00:05:01,669
A matching visible count protects you from celebrating the right dollar

86
00:05:01,669 --> 00:05:04,106
answer for the wrong subset of orders.

87
00:05:04,706 --> 00:05:08,686
For a dashboard that responds only to a region filter, code

88
00:05:08,686 --> 00:05:12,665
nine is enough, and it will still include any manually hidden

89
00:05:12,665 --> 00:05:14,474
row within the selected region.

90
00:05:14,474 --> 00:05:18,816
If a teammate manually hides rows to exclude them from today's

91
00:05:18,816 --> 00:05:23,157
working view, code one-oh-nine is the safer visible-row total.

92
00:05:23,157 --> 00:05:27,137
Keep the code choice visible in the sheet notes so someone

93
00:05:27,137 --> 00:05:30,755
reading four hundred versus three hundred fifty-five understands why.

94
00:05:30,755 --> 00:05:35,458
Neither formula changes source amounts; both recalculate from the same six fictional cells.

95
00:05:35,458 --> 00:05:36,182
Choose deliberately.

96
00:05:36,182 --> 00:05:41,970
Code one-oh-nine is often easier to explain as a total of everything actually visible.

97
00:05:41,970 --> 00:05:45,950
Code nine is appropriate when manual hiding is only a personal

98
00:05:45,950 --> 00:05:48,844
viewing convenience and those orders should still count.

99
00:05:48,844 --> 00:05:53,186
The filter-only East and West answers match under both codes; the

100
00:05:53,186 --> 00:05:55,357
manual-hide case reveals the difference.

101
00:05:55,957 --> 00:05:59,923
If both SUBTOTAL cells show five hundred forty under the East

102
00:05:59,923 --> 00:06:04,970
filter, check the filter icon and whether you hid rows by a different method.

103
00:06:04,970 --> 00:06:08,937
If a value is blank or stored as text, reconcile C

104
00:06:08,937 --> 00:06:12,903
two through C seven against the CSV before changing the formula.

105
00:06:12,903 --> 00:06:16,869
If you are making a dashboard, keep the calculation cell outside

106
00:06:16,869 --> 00:06:18,311
the filtered data block.

107
00:06:18,311 --> 00:06:22,638
Do not replace a bad result with a hard-coded four hundred.

108
00:06:22,638 --> 00:06:26,604
If the formula appears literally as text instead of calculating, inspect

109
00:06:26,604 --> 00:06:31,291
whether the cell is formatted as plain text or starts with an apostrophe.

110
00:06:31,291 --> 00:06:33,454
Fix that separate input problem first.

111
00:06:33,454 --> 00:06:37,420
An incorrect value should be investigated in the sheet, never patched

112
00:06:37,420 --> 00:06:40,665
by typing the expected dollar amount over the formula.

113
00:06:41,265 --> 00:06:43,228
The final check has three states.

114
00:06:43,228 --> 00:06:46,500
With no filter, the six orders total five hundred forty.

115
00:06:46,500 --> 00:06:50,425
East yields four hundred, and West yields one hundred forty in SUBTOTAL.

116
00:06:50,425 --> 00:06:54,351
Under the East filter, manually hiding the forty-five-dollar row makes

117
00:06:54,351 --> 00:06:59,586
code one-oh-nine yield three hundred fifty-five, while code nine still yields four hundred.

118
00:06:59,586 --> 00:07:02,530
Unhide the row and clear the filter when finished.

119
00:07:02,530 --> 00:07:07,437
You now have a reusable visible-row total and know what each code actually excludes.