Health and family

Team spending by category: a simple report template for small crews

Build a team spending by category report in Excel or Google Sheets with SUMIFS and a pivot table, with a worked example for a small crew.

Editorial illustration for Team spending by category: a simple report template for small crews

To see team spend by category, keep one table with date, person, category and amount, then use a pivot table or SUMIFS formulas to total each category per person. Both take five minutes once the table is clean. The formulas below work in Excel and Google Sheets.

How should the data table look?

Put headers in row 1 and keep one row per expense. Use these columns: A Date, B Person, C Category, D Amount. Use a fixed list of categories, for example Fuel, Phone, Repairs, Food, Parking and Other, and apply a dropdown to column C so spellings stay identical. Avoid blank rows and merged cells.

How do you total by category with SUMIFS?

Write the category names down column F, starting at F2, and the people across row 1 from G1. Then in G2 enter this formula and copy it across and down.

  • =SUMIFS($D$2:$D$500, $C$2:$C$500, $F2, $B$2:$B$500, G$1)
  • The first range is what you add up (Amount), then each pair is a range and the value it must match.
  • The dollar signs lock the ranges so they do not slide when you copy the formula.
  • For a category total across all people, use =SUMIFS($D$2:$D$500, $C$2:$C$500, $F2).

How do you limit it to one month?

Add start and end dates in cells J1 and J2. Then extend the formula with two more conditions.

  • =SUMIFS($D$2:$D$500, $C$2:$C$500, $F2, $B$2:$B$500, G$1, $A$2:$A$500, ">="&$J$1, $A$2:$A$500, "<="&$J$2)
  • Dates must be real dates, not text. If a column left-aligns after you paste it, it is probably text.
  • A pivot table gives the same answer with drag and drop: Category in rows, Person in columns, Amount in values.

What does a worked example look like?

Say a crew of three riders logs these made-up totals for a month.

CategoryRavi (₹)Salma (₹)Joseph (₹)Row total (₹)
Fuel4,2003,8004,60012,600
Phone6006006501,850
Repairs1,50002,2003,700
Other300450150900
Person total6,6004,8507,60019,050

Check the arithmetic both ways. Rows: 12,600 plus 1,850 plus 3,700 plus 900 is 19,050. Columns: 6,600 plus 4,850 plus 7,600 is 19,050. If the two totals differ, a row has a category typed differently, such as Fuel and Petrol, and SUMIFS has skipped it.

When does a sheet stop being enough?

Once you have a few hundred rows a month, typing categories by hand becomes the weak link. SpendixAI can import a bank or card statement, apply your categorisation rules and show spending grouped by category in its reports, for your own workspace. It is a personal-workspace product, not a team tool, so a shared crew sheet is still the right place for per-person totals.

How do you read the report once it is built?

Look at three things. First, the biggest category: if fuel is two thirds of the spend, a small saving there matters more than anything else. Second, the odd one out: a person whose repairs are three times the others may have an older vehicle or may be miscategorising. Third, the trend: put last month's grid next to this month's and see which row moved. A report is only useful if it leads to one question you can ask the crew.

What should you do next?

  1. Fix the category list and add the dropdown.
  2. Paste a month of data and run the SUMIFS grid.
  3. Add the check cell.
  4. Share the sheet view-only with the crew so everyone can see their own line.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports