Health and family

Team spending by category report: counts, totals and averages in A$

A team spending by category report for Australian dollars that shows claim counts, totals and averages per category, plus a check for outliers and blanks.

Editorial illustration for Team spending by category report: counts, totals and averages in A$

A category total tells you how much. Add a count and an average per category and it tells you what kind of spending it is: many small claims or a few large ones. This layout suits a team on the road, such as sales reps, and uses Australian dollars. It takes a flat list and three formulas.

What do the three columns mean?

  • Count: how many claims fell in the category.
  • Total: the sum of their amounts.
  • Average: total divided by count.

With the source list in columns A Date, B Category, C Member and D Amount, put each category name in F2 downward and use these in G, H and I:

  • Count: =COUNTIF($B$2:$B$500,$F2)
  • Total: =SUMIF($B$2:$B$500,$F2,$D$2:$D$500)
  • Average: =H2/G2

What does a finished report look like?

Made-up figures for one month of a sales team:

CategoryClaimsTotalAverage
Fuel22A$1,650A$75
Accommodation6A$2,400A$400
Meals31A$930A$30
Parking and tolls18A$270A$15
All77A$5,250A$68.18

Check: 22 + 6 + 31 + 18 = 77 claims; 1,650 + 2,400 + 930 + 270 = A$5,250; and 5,250 / 77 = A$68.18 on average. The overall average is less useful than the per-category one, which is the point of the table.

How do I spot the odd ones out?

Totals can hide a single strange row. Three simple checks sit well under the table:

  1. Largest claim: =LARGE($D$2:$D$500,1). Change 1 to 2 and 3 for the next two.
  2. Blank categories: =COUNTBLANK($B$2:$B$500). It should be 0.
  3. Check against the bank: add up the list and compare with the card statement for the same period.

If accommodation shows one A$400 average but a single claim of A$1,100, look at that row first. It may be a team booking charged to one person.

Who should see the report, and what should it say?

Share it with the team, not only the manager. People accept a report that shows everyone the same table. Add two lines: the category that moved the most and one thing you will do about it, such as agreeing a rate for parking.

When does a tool help more than a sheet?

Once claims arrive daily from several people, with receipts attached and categories chosen on the spot, a spreadsheet becomes the bottleneck. A tool that groups spend by category and member with receipts attached lifts that load. SpendixAI is built around India and INR, so we do not offer it here for Australian teams; Pro also covers one personal workspace, not a team.

How do I use the count to set a rule?

If meals are 31 claims averaging A$30, a per-person daily limit is easier to explain than checking each receipt. If accommodation is six claims averaging A$400, pre-approval of bookings makes more sense. The count tells you whether to control by rule or by review.

Write the rule down, try it for a month and compare the next report to see whether claims moved. If the count stays the same and the average falls, the rule is working. If the count rises, people may be splitting claims, which is worth a gentle word.

How do I compare this month with last month?

Copy the table to a new tab each month and add a column for the change in total: this month's total minus last month's. For fuel, a move from A$1,650 to A$1,900 is +A$250. Do the same for the count, since a rise in count with a flat average means more claims, while a flat count with a higher average means each claim is bigger. Those are different stories and need different conversations.

Month-end list

  • COUNTBLANK is zero.
  • Total matches the statement.
  • Three largest claims reviewed.
  • Two-line note written.
SPENDIXAI JOURNAL

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

Explore your reports