In this video, you will calculate the amount spent in each category from the banking statement simulation.
To do this, you’ll use SUMIF formulas and absolute cell references.
Categorizing expenditures makes it easier to identify what you are spending your money on.
And absolute cell references allow you to copy formulas throughout your spreadsheet.
First, create a “by category” column in your summary table.
Then, calculate the total amount you spent in each category. To do this, you could go through the spreadsheet and manually select all of the cells with “Utilities” values and add them up; then find the “Restaurant” values and sum those, and so on.
But what happens if you move something around?
What about next month when your expenditures change?
Or what if you just have a TON of expenditures?
That’s a lot of work!
The SUMIF formula helps with this.
SUMIF adds all of the values IF they are in a certain category.
Beside “Utilities” in the summary table, type “ = SUMIF.”
You can select “SUMIF” when the autofill menu appears.
There are three parts, or arguments, in a SUMIF formula.
They are separated by commas.
The first is the cell range that contains the category names.
In this case, you listed the categories in Column F, beginning with row 12.
Type F12, colon, F to indicate this range.
Add a comma after the F. The range highlights in your spreadsheet.
Next, add the cell reference that contains the category name.
In this case, select the cell with the word “Utilities,” then type a comma.
Finally, add the sum range, or the range of cells with the values you want to add.
In this case, the values are in the D Column, starting with D12.
Type D12, colon, D and close the parentheses.
Be sure there are commas between each argument in your formula.
Then, press Enter.
The spreadsheet adds all of the expenses labeled “Utilities,” and the total appears in this cell.
Double click on the formula cell.
Be sure the formula references the appropriate cells or range.
Check the math.
Look at the columns you labeled “Utilities”-- or whatever category you specified.
Add these costs.
It should match the total in the spreadsheet.
Next, copy the formula to other cells.
Drag the cell handle to extend the formula to the rest of the categories.
Something isn’t working.
Check the formula bar.
When you changed the cell reference--the middle argument--to E5, the cell range and the sum range both shifted down, too.
Now, instead of including the entire column, they begin on row 14.
Typical cell references are relative.
That means they change when a formula is copied to another cell.
Because you changed the cell reference with the name of the category, the spreadsheet assumed that the other cells would also shift down.
To fix this, add an absolute cell reference to the formula.
Absolute references remain constant, even when you copy them to another cell or sheet.
Absolute references are important when you want to reference the same cell or range in multiple formulas.
To create an absolute cell reference, use dollar signs before the column letter and before the row number.
This “locks” the cell reference so that it does not shift with the rest of the formula.
In this case, the cell range AND the sum range remain constant.
Add dollar signs in the cell range before the F and before the 12.
Then, add dollar signs to the sum range, before the D and before the 12.
Now the calculations are correct, and you can drag the cell handle to copy the formula to other categories.
As you build more complex spreadsheets, absolute cell references become even more important.
Adding absolute cell references for cells that will remain constant allows you to quickly and easily copy formulas to other categories.
Now, it’s your turn: Create SUMIF formulas to calculate the total expenditures in one category.
Add absolute references to lock cell references.
Copy the formula to the rest of the categories.
Then, move on to the next video.
1. Develop a Budget
2. Copy Data and Create a Summary Table
3. Calculate Total Expenditures
4. Categorize Expenditures
5. Label and Calculate Category Totals
6. Use Data Validation to Make Your Spreadsheet More Reliable
7. Visualize the Data and Make Decisions
8. Develop a Budget Wrap Up
- Create SUMIF formulas to calculate the total expenditures in one category.
- Add absolute references to lock cell references.
- Copy the formula to the rest of the categories.