Sumifs: The one Formula every Tour Managers needs to know.
In this post, we talk about using the sumifs formula to sum across categories in your accounting sheet and to make sure your actuals are updated.
Let's face it: Tour accounting and updating actuals can feel like a never-ending battle against numbers, and every trick that can help you save time is worth it. Enter the SUMIFS function, a surprisingly simple tool that can transform your budgeting and tour accounting process.
Why SUMIFS is a Tour Manager's Best Friend
Imagine you have a detailed accounting sheet where every expense is meticulously recorded. Now, imagine you could instantly see how much you've spent on hotels, travel, production, or any other category. That's the magic of SUMIFS! It allows you to add up values based on multiple criteria, making it a budgeting powerhouse.
By the way, if you don't have a tour budget spreadsheet ready yet, we've got one you can download for free here.
How SUMIFS Works (No Spreadsheet Degree Required)
Don't worry, you don't need to be an Excel whiz to grasp this. Here's the basic SUMIFS formula:
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)
Here's the breakdown:
- sum_range: The range of cells you want to add up (e.g., your expense amounts).
- criteria_range1: The range of cells containing your first criterion (e.g., your expense categories).
- criterion1: The specific value you want to match in the first criteria range (e.g., "hotel").
- [criteria_range2, criterion2], ... (optional): Add more criteria to filter your results further (e.g., by date or location).
Real-World Example: Taming Your Accounting Sheet
Let's say your tour accounting sheet looks like this:
Now let's say you have your budget in a different tab, and you want to track your total spent right next to your budgeted amounts, per category.
Now, to calculate the total spent on accommodation, you'd use this SUMIFS formula in the "Total Spent" column next to "Accommodation":
=SUMIFS(Expenses!D:D, Expenses!C:C, "Accommodation")
- Expenses!D:D: The range containing your expense amounts (on the "Expenses" sheet).
- Expenses!C:C: The range containing your expense categories (on the "Expenses" sheet).
- "Accommodation": The category you want to filter by.
This formula will magically add up all expenses labeled as "Accommodation" and display the total in your summary table. Repeat the process for other categories, adjusting the criterion accordingly.
Streamline it even more by referring to the category dynamically
To streamline your formulas and make them even more intuitive, you can skip typing the category name (like "Accommodation") directly into the formula. Instead, reference the cell that contains the category name in your summary table.
For example, if the word "Accommodation" is in cell A2 of your summary table, your SUMIFS formula would become:
=SUMIFS(Expenses!D:D, Expenses!C:C, A2)
Now, you can simply apply the formula to the entire column; if you drag the formula down you'll reference cell A3, A4,... which have the other categories, and you're basically done.
This approach makes it easier to track spending across different categories without having to manually edit multiple formulas.
Bonus Tip: Streamline with The Pack
While SUMIFS is awesome, The Pack takes it to the next level. Our platform automates expense tracking, generates detailed reports, and even allows you to collaborate with others on preparing your tour accounting. With The Pack, you can ditch the spreadsheets and focus on what you do best: creating unforgettable live experiences.
Ready to simplify your tour budgeting and free yourself from spreadsheet headaches? Give SUMIFS a try, and discover how easy it is to gain control of your tour finances!
This post is part of a series on Mastering Spreadsheets.
Other posts in the series:
Drop us a line if you miss anything in the series, we'd happily put our resident spreadsheet expert to work!