In this video, you will calculate total expenditures 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, 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 sum them; then the cells with “Restaurants” values and sum them, and so on.
But what happens if you move something around?
What happens next month when your expenditures change?
Or what if you just have a TON of expenditures?
The SUMIF formula helps with this.
SUMIF is a conditional formula.
That means it will sum all of the values IF they are of a certain category Beside “Utilities” in the summary table, type “Equals SUMIF.”
You can also select “SUMIF” when the autofill menu appears.
SUMIF requires three arguments, each 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 F-12, colon, F to indicate this range.
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.”
Finally, add the sum range, or the range of cells with the values you want to add.
In this case, the values are in Column D, 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 cell with the formula to see all cells referenced in the formula by color.
Ensure the formula is referencing the appropriate cell 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.
In previous spreadsheets, you dragged 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 a value to remain constant in a spreadsheet.
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
9. CCE Plan and Budget Activity 3 Reflection
- 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.