WEBVTT
Kind: captions
Language: en
00:00:00.520 --> 00:00:05.320
In this video, you will calculate the amount
spent in each category
00:00:05.320 --> 00:00:08.000
from the banking statement simulation.
00:00:08.160 --> 00:00:13.260
To do this, you’ll use SUMIF formulas and
absolute cell references.
00:00:14.280 --> 00:00:18.120
Categorizing expenditures makes it easier
to identify
00:00:18.120 --> 00:00:20.320
what you are spending your money on.
00:00:20.440 --> 00:00:26.060
And absolute cell references allow you to
copy formulas throughout your spreadsheet.
00:00:26.360 --> 00:00:31.160
First, create a “by category” column in
your summary table.
00:00:31.680 --> 00:00:38.040
Then, calculate the total amount you spent in each category. To do this, you could
00:00:38.040 --> 00:00:43.660
go through the spreadsheet and manually select
all of the cells with “Utilities” values
00:00:43.670 --> 00:00:50.010
and add them up; then find the “Restaurant”
values and sum those, and so on.
00:00:50.010 --> 00:00:53.020
But what happens if you move something around?
00:00:53.460 --> 00:00:56.480
What about next month when your expenditures
change?
00:00:56.489 --> 00:00:59.760
Or what if you just have a TON of expenditures?
00:00:59.760 --> 00:01:02.170
That’s a lot of work!
00:01:02.170 --> 00:01:05.430
The SUMIF formula helps with this.
00:01:05.430 --> 00:01:10.360
SUMIF adds all of the values IF they are in
a certain category.
00:01:11.060 --> 00:01:16.040
Beside “Utilities” in the summary table,
type “ = SUMIF.”
00:01:16.400 --> 00:01:20.700
You can select “SUMIF” when the autofill
menu appears.
00:01:21.760 --> 00:01:26.740
There are three parts, or arguments, in a
SUMIF formula.
00:01:26.749 --> 00:01:28.930
They are separated by commas.
00:01:28.930 --> 00:01:32.680
The first is the cell range that contains
the category names.
00:01:33.120 --> 00:01:38.960
In this case, you listed the categories in
Column F, beginning with row 12.
00:01:39.860 --> 00:01:45.120
Type F12, colon, F to indicate this range.
00:01:45.980 --> 00:01:50.540
Add a comma after the F. The range highlights
in your spreadsheet.
00:01:50.880 --> 00:01:55.600
Next, add the cell reference that contains
the category name.
00:01:56.060 --> 00:02:01.380
In this case, select the cell with the word
“Utilities,” then type a comma.
00:02:02.180 --> 00:02:08.520
Finally, add the sum range, or the range of
cells with the values you want to add.
00:02:08.520 --> 00:02:14.800
In this case, the values are in the D Column,
starting with D12.
00:02:15.240 --> 00:02:19.140
Type D12, colon, D and close the parentheses.
00:02:19.600 --> 00:02:23.540
Be sure there are commas between each argument
in your formula.
00:02:23.540 --> 00:02:25.560
Then, press Enter.
00:02:26.340 --> 00:02:30.180
The spreadsheet adds all of the expenses
labeled “Utilities,”
00:02:30.180 --> 00:02:33.400
and the total appears in this cell.
00:02:33.780 --> 00:02:36.000
Double click on the formula cell.
00:02:36.000 --> 00:02:40.700
Be sure the formula references the appropriate
cells or range.
00:02:41.700 --> 00:02:42.800
Check the math.
00:02:44.020 --> 00:02:49.400
Look at the columns you labeled “Utilities”-- or
whatever category you specified.
00:02:49.840 --> 00:02:50.980
Add these costs.
00:02:51.400 --> 00:02:54.460
It should match the total in the spreadsheet.
00:02:54.470 --> 00:02:57.900
Next, copy the formula to other cells.
00:02:57.900 --> 00:03:03.240
Drag the cell handle to extend the formula
to the rest of the categories.
00:03:03.240 --> 00:03:04.460
Uh oh!
00:03:04.460 --> 00:03:05.800
Something isn’t working.
00:03:07.820 --> 00:03:09.480
Check the formula bar.
00:03:10.100 --> 00:03:15.000
When you changed the cell reference--the middle
argument--to E5,
00:03:15.000 --> 00:03:19.200
the cell range and the sum range both shifted down, too.
00:03:19.480 --> 00:03:25.220
Now, instead of including the entire column,
they begin on row 14.
00:03:25.680 --> 00:03:26.720
What happened?
00:03:27.400 --> 00:03:30.060
Typical cell references are relative.
00:03:30.060 --> 00:03:33.780
That means they change when a formula is copied
to another cell.
00:03:34.100 --> 00:03:38.780
Because you changed the cell reference with
the name of the category, the spreadsheet
00:03:38.780 --> 00:03:42.060
assumed that the other cells would also shift
down.
00:03:42.640 --> 00:03:46.700
To fix this, add an absolute cell reference
to the formula.
00:03:46.710 --> 00:03:53.440
Absolute references remain constant, even
when you copy them to another cell or sheet.
00:03:53.440 --> 00:03:57.900
Absolute references are important when you
want to reference the same cell
00:03:57.900 --> 00:04:00.540
or range in multiple formulas.
00:04:01.080 --> 00:04:06.420
To create an absolute cell reference, use
dollar signs before the column letter and
00:04:06.420 --> 00:04:08.420
before the row number.
00:04:08.560 --> 00:04:13.260
This “locks” the cell reference so that
it does not shift with the rest of the formula.
00:04:14.180 --> 00:04:19.980
In this case, the cell range AND the sum range
remain constant.
00:04:21.220 --> 00:04:26.260
Add dollar signs in the cell range before
the F and before the 12.
00:04:27.760 --> 00:04:33.640
Then, add dollar signs to the sum range, before
the D and before the 12.
00:04:35.140 --> 00:04:39.120
Now the calculations are correct, and you
can drag the cell handle
00:04:39.120 --> 00:04:42.300
to copy the formula to other categories.
00:04:43.460 --> 00:04:50.520
As you build more complex spreadsheets, absolute
cell references become even more important.
00:04:51.020 --> 00:04:56.759
Adding absolute cell references for cells
that will remain constant allows you to quickly
00:04:56.760 --> 00:05:00.200
and easily copy formulas to other categories.
00:05:01.000 --> 00:05:02.240
Now, it’s your turn:
00:05:02.920 --> 00:05:08.480
Create SUMIF formulas to calculate the total expenditures in one category.
00:05:09.100 --> 00:05:12.380
Add absolute references to lock cell references.
00:05:13.360 --> 00:05:15.920
Copy the formula to the rest of the categories.
00:05:16.500 --> 00:05:18.940
Then, move on to the next video.