Health and family

Team spending by category report: a monthly grid for a Canadian crew

Build a monthly team spending by category report in Canadian dollars, with a SUMIFS and EDATE grid for a site crew and a worked example with totals.

Editorial illustration for Team spending by category report: a monthly grid for a Canadian crew

If your team spends across the season, the report to build is a grid with categories down the side and months across the top. One SUMIFS formula with a start and end date fills every cell. It shows a manager where money goes and which month jumped, using Canadian dollars in this example.

What does the source list look like?

Four columns on a sheet called Data: A Date, B Category, C Crew member or job, D Amount in C$. Enter every expense on its own row, one category each. A single row split across two categories is the usual cause of a total that will not match.

Which formula fills the monthly grid?

On a sheet called Grid, put categories in F2:F4 and the first day of each month in G1, H1 and I1 as real dates, for example 1 January. In G2 enter this and fill right and down:

  • =SUMIFS(Data!$D$2:$D$900, Data!$B$2:$B$900, $F2, Data!$A$2:$A$900, ">="&G$1, Data!$A$2:$A$900, "<"&EDATE(G$1,1))
  • Month total at the bottom: =SUM(G2:G4)
  • Category total at the right: =SUM(G2:I2)

The formula adds amounts for the category where the date is on or after the first of the month and before the first of the next. EDATE works in Excel and Google Sheets. If a date was typed as text, the formula will return zero, so check the cells are right-aligned numbers.

What does the finished grid show?

Made-up numbers for an eight-person site crew:

CategoryJanuaryFebruaryMarchTotal
FuelC$1,200C$980C$1,340C$3,520
MaterialsC$4,100C$3,650C$5,200C$12,950
Safety gearC$260C$0C$410C$670
Month totalC$5,560C$4,630C$6,950C$17,140

Rows add up to 3,520 + 12,950 + 670 = 17,140 and columns to 5,560 + 4,630 + 6,950 = 17,140, so both agree.

How do I read a grid like this?

  • March is C$2,320 above February (6,950 - 4,630). Materials explain C$1,550 of that, and safety gear C$410.
  • Fuel moves with the work, so compare it with the number of jobs or kilometres driven, not only with last month.
  • A zero, like February's safety gear, can mean nothing was bought or nothing was entered. Ask.

When should I move beyond a spreadsheet?

When several foremen enter spending from site, a shared sheet gets messy: duplicate rows, missing categories, receipts in someone's phone. Look for a tool that groups spend by category and by member and attaches receipts. SpendixAI offers grouped reports of that kind, but it is built around India and INR, so we do not present it as a Canadian option, and Pro covers one personal workspace rather than a team.

How do I split materials by job?

Add a Job column beside Member and build a second grid with jobs across the top instead of months. The same SUMIFS pattern applies: swap the date conditions for a job name. A month grid shows when money moved; a job grid shows what each project cost.

If materials were bought for two jobs on one receipt, enter two rows, one per job, so each grid stays honest. Keep the receipt reference the same on both rows so you can find the original.

When the crew leader changes mid-month, keep entering under the job, not the person, so the history stays with the work.

How do I keep the grid up to date as the season runs?

Add a new month column each time by copying the last one and changing only the date in the header. Because the formula reads the header, nothing else needs editing. Extend the source ranges, for example from row 900 to row 2000, when the list grows past the end. A quick check each month is to compare the grand total with the sum of all amounts in the Data sheet, which should be identical.

A short routine

  • Weekly: enter or collect receipts.
  • Month end: fix blank categories, refresh the grid.
  • After sharing: note one explanation per big jump.
SPENDIXAI JOURNAL

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

Explore your reports