If you work in accounting or finance, the SUMIFS function is one of the most powerful tools in Excel for creating quick summaries without a pivot table.
It lets you sum numbers based on multiple conditions, which is perfect for reports like department totals, budget vs actual, and monthly expense tracking.
In this guide, we’ll:
- Use a realistic finance dataset (download link below).
- Walk through 20 SUMIFS practice problems (easy → advanced).
- Provide the answers so you can check your work.
- Show how PivotXL can automate the same process — without writing formulas.
The Practice Dataset
Our sample data represents Monthly Department Expenses in a mid-sized company.
| Date | Department | Account Category | Amount | Payment Method | Region |
|---|---|---|---|---|---|
| 05-Jan-2025 | Sales | Travel | 850 | Credit Card | East |
| 08-Jan-2025 | Marketing | Software | 1200 | Bank Transfer | West |
| 15-Jan-2025 | HR | Salaries | 5000 | Bank Transfer | North |
| 20-Jan-2025 | IT | Office Supplies | 300 | Credit Card | South |
| 28-Jan-2025 | Operations | Utilities | 700 | Cash | East |
| 04-Feb-2025 | Sales | Salaries | 4800 | Bank Transfer | East |
| 10-Feb-2025 | Marketing | Travel | 950 | Credit Card | West |
| 18-Feb-2025 | HR | Office Supplies | 400 | Credit Card | North |
| 23-Feb-2025 | IT | Software | 2500 | Bank Transfer | South |
| 27-Feb-2025 | Operations | Utilities | 750 | Bank Transfer | East |
📥 Download the dataset in Excel
Part 1: Beginner SUMIFS Problems
These use one or two conditions.
- Total Travel expenses across all departments.
- Total expenses for the Sales department.
- Total Utilities expenses in the East region.
- Total expenses paid via Credit Card.
- Total Salaries for the HR department.
Part 2: Intermediate SUMIFS Problems
These use two to three conditions.
- Total Software expenses in January 2025.
- Total Travel expenses in West region using Credit Card.
- Total expenses for IT department in February 2025.
- Total Office Supplies in North region.
- Total Utilities expenses paid via Bank Transfer.
Part 3: Advanced SUMIFS Problems
These use date ranges + multiple conditions.
- Total expenses between 01-Jan-2025 and 31-Jan-2025 for the East region.
- Total Salaries in the East region in February 2025.
- Total Travel expenses for Sales department in January 2025.
- Total Software expenses in South region for all months.
- Total Office Supplies in January 2025 paid by Credit Card.
- Total Utilities in East region for Feb 2025 only.
- Total expenses for Marketing department excluding Travel.
- Total Salaries for any department in North region.
- Total expenses in Feb 2025 for all Bank Transfer payments.
- Total expenses in Feb 2025 for Sales and IT combined.
Answer Key
| Problem | SUMIFS Formula |
|---|---|
| 1 | =SUMIFS(D:D,C:C,"Travel") |
| 2 | =SUMIFS(D:D,B:B,"Sales") |
| 3 | =SUMIFS(D:D,C:C,"Utilities",F:F,"East") |
| 4 | =SUMIFS(D:D,E:E,"Credit Card") |
| 5 | =SUMIFS(D:D,B:B,"HR",C:C,"Salaries") |
| 6 | =SUMIFS(D:D,C:C,"Software",A:A,">=01-Jan-2025",A:A,"<=31-Jan-2025") |
| 7 | =SUMIFS(D:D,C:C,"Travel",F:F,"West",E:E,"Credit Card") |
| 8 | =SUMIFS(D:D,B:B,"IT",A:A,">=01-Feb-2025",A:A,"<=28-Feb-2025") |
| 9 | =SUMIFS(D:D,C:C,"Office Supplies",F:F,"North") |
| 10 | =SUMIFS(D:D,C:C,"Utilities",E:E,"Bank Transfer") |
| 11 | =SUMIFS(D:D,A:A,">=01-Jan-2025",A:A,"<=31-Jan-2025",F:F,"East") |
| 12 | =SUMIFS(D:D,C:C,"Salaries",F:F,"East",A:A,">=01-Feb-2025",A:A,"<=28-Feb-2025") |
| 13 | =SUMIFS(D:D,C:C,"Travel",B:B,"Sales",A:A,">=01-Jan-2025",A:A,"<=31-Jan-2025") |
| 14 | =SUMIFS(D:D,C:C,"Software",F:F,"South") |
| 15 | =SUMIFS(D:D,C:C,"Office Supplies",A:A,">=01-Jan-2025",A:A,"<=31-Jan-2025",E:E,"Credit Card") |
| 16 | =SUMIFS(D:D,C:C,"Utilities",F:F,"East",A:A,">=01-Feb-2025",A:A,"<=28-Feb-2025") |
| 17 | =SUMIFS(D:D,B:B,"Marketing",C:C,"<>Travel") |
| 18 | =SUMIFS(D:D,C:C,"Salaries",F:F,"North") |
| 19 | =SUMIFS(D:D,A:A,">=01-Feb-2025",A:A,"<=28-Feb-2025",E:E,"Bank Transfer") |
| 20 | =SUMIFS(D:D,B:B,"Sales",A:A,">=01-Feb-2025",A:A,"<=28-Feb-2025") + SUMIFS(D:D,B:B,"IT",A:A,">=01-Feb-2025",A:A,"<=28-Feb-2025") |
From SUMIFS to Automation
Writing SUMIFS is great for learning and one-off analysis — but in real finance teams, this can turn into:
- 30+ formulas across multiple tabs
- Manual data refreshes every month
- Broken links when columns shift
PivotXL connects directly to your accounting system (QuickBooks, Xero, Sage, etc.),
pulls in the same data you just practiced on,
and lets you refresh all your totals instantly — without touching a formula.
📌 Next Step: See how PivotXL automates Excel-based reporting
→ Related Read: How to Prepare Financial Statements from Trial Balance – A real-world example of using SUMIFS for accountants and finance professionals.
Follow Us on YouTube
Stay updated with PivotXL’s latest walkthroughs and features:
Or visit the full channel: PivotXL on YouTube to explore all our Excel-based FP&A tutorials.
