Use SUMIF with the category column as the range, the category name as the criterion and the amount column as the sum range, for example =SUMIF(C2:C500,"Supplies",D2:D500) in Google Sheets or Excel. It adds every amount in rows 2 to 500 whose category is Supplies. ProfitDrawer's bookkeeping spreadsheets total each category with SUMIFS, the multi-condition version, so one formula can also limit totals to a month.
Syntax: SUMIF(range, criterion, [sum_range])
- range: the cells tested, here the category column.
- criterion: the condition. Text goes in quotation marks, for example
"Supplies", and can use wildcards. Numbers can use=,>and<, for example">20". - sum_range: the cells to add, here the amounts. Leave it out only if you are adding the same cells you test.
Example layout (Expenses sheet): A = date, B = paid to, C = category, D = amount.
| Category | Total formula |
|---|---|
| Supplies | =SUMIF($C$2:$C$500,"Supplies",$D$2:$D$500) |
| Fuel | =SUMIF($C$2:$C$500,"Fuel",$D$2:$D$500) |
The dollar signs keep the ranges fixed if you copy the formula to another cell.
Totals by category and month: use SUMIFS, written SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]), so you can add a second test such as a date limit. Keep category spelling identical in every row (a dropdown helps), since "Supply" and "Supplies" are counted separately.
Source: Google Docs Editors Help: SUMIF function · Updated
Tools and spreadsheets for this
Related questions
- How do I keep track of business expenses in a spreadsheet?
- How do I add a drop-down list in Google Sheets?
- How do I keep track of business expenses as a small business owner?
- What business expenses are tax deductible?
- What is in a ProfitDrawer bookkeeping spreadsheet?
- How do I open an Excel file in Google Sheets?
All questions on ProfitDrawer Answers →
General information, not tax, legal or financial advice. Check the rules for your state and your situation.