1
00:00:00,080 --> 00:00:05,411
A Google Sheets total misses three amounts that look numeric but are actually text.

2
00:00:05,411 --> 00:00:09,981
In our controlled practice, twelve, eight and five must total twenty-five.

3
00:00:09,981 --> 00:00:14,170
We use TO_TEXT to reproduce the symptom reliably, then VALUE in

4
00:00:14,170 --> 00:00:16,836
a helper column to convert each value.

5
00:00:16,836 --> 00:00:21,786
The result is a checked numeric total without destroying the original source column.

6
00:00:22,386 --> 00:00:26,441
In a private Google Sheet, enter the three TO_TEXT formulas from

7
00:00:26,441 --> 00:00:28,653
the practice file in A2:A4.

8
00:00:28,653 --> 00:00:32,708
This deliberately makes strings that look like twelve, eight and five.

9
00:00:32,708 --> 00:00:37,132
A real CSV import may be auto-converted, so the practice file

10
00:00:37,132 --> 00:00:39,712
uses formulas to guarantee the starting state.

11
00:00:39,712 --> 00:00:44,136
Keep the original values in column A while testing a helper column.

12
00:00:44,736 --> 00:00:49,821
Enter =SUM(A2:A4) in a separate scratch cell and compare it with

13
00:00:49,821 --> 00:00:51,777
the arithmetic answer twenty-five.

14
00:00:51,777 --> 00:00:56,081
If the controlled source is text, the sum does not include

15
00:00:56,081 --> 00:00:58,036
those text values as intended.

16
00:00:58,036 --> 00:01:02,340
Do not judge from alignment or cell color alone; inspect a

17
00:01:02,340 --> 00:01:05,078
source formula and confirm TO_TEXT is present.

18
00:01:05,678 --> 00:01:10,224
In B2 enter =VALUE(A2), then fill B3 and B4.

19
00:01:10,224 --> 00:01:14,769
Google's VALUE function converts recognized numeric strings into numbers.

20
00:01:14,769 --> 00:01:19,315
Check each helper output before totaling: twelve, eight and five.

21
00:01:19,315 --> 00:01:23,860
If a real imported amount contains unexpected characters, VALUE may

22
00:01:23,860 --> 00:01:29,315
error; investigate the content and locale rather than wrapping everything in IFERROR.

23
00:01:29,915 --> 00:01:32,403
Enter =SUM(B2:B4) in B5.

24
00:01:32,403 --> 00:01:34,477
It should return twenty-five.

25
00:01:34,477 --> 00:01:38,624
Compare this with twelve plus eight plus five calculated independently.

26
00:01:38,624 --> 00:01:43,186
Keep the original text column until you have checked every row;

27
00:01:43,186 --> 00:01:47,748
replacing source values too early makes it harder to find a

28
00:01:47,748 --> 00:01:50,236
bad import or a changed separator.

29
00:01:50,836 --> 00:01:55,044
Open the failing cell and inspect spaces, currency symbols, thousands

30
00:01:55,044 --> 00:01:57,990
and decimal separators, and the spreadsheet locale.

31
00:01:57,990 --> 00:02:02,198
VALUE supports formats the sheet understands but cannot turn arbitrary

32
00:02:02,198 --> 00:02:03,881
labels into valid numbers.

33
00:02:03,881 --> 00:02:08,510
Fix the source specification or use a documented cleaning step for

34
00:02:08,510 --> 00:02:12,297
that particular format before you rerun the row check.

35
00:02:12,897 --> 00:02:16,550
Compare your own practice copy with the answer check.

36
00:02:16,550 --> 00:02:21,420
The controlled A2:A4 values are strings created with TO_TEXT; the VALUE

37
00:02:21,420 --> 00:02:25,884
helper in B2:B4 yields the numbers 12, 8 and 5.

38
00:02:25,884 --> 00:02:29,130
SUM of the converted helper range is 25.

39
00:02:29,130 --> 00:02:33,595
If VALUE produces an error for real imported values, stop and

40
00:02:33,595 --> 00:02:37,653
inspect their symbols, separators and locale instead of forcing a

41
00:02:37,653 --> 00:02:39,682
conversion or claiming an answer.

42
00:02:39,682 --> 00:02:44,146
If the result differs, revisit the source data and the specific

43
00:02:44,146 --> 00:02:46,581
step rather than hiding the symptom.

44
00:02:46,581 --> 00:02:51,045
The matching written guide includes the exercise and primary product references.