Using SUMIF Across Multiple Sheets in Excel
This tutorial covers how to use SUMIF across multiple sheets in Excel by combining it with SUMPRODUCT and INDIRECT functions. This approach lets you dynamically sum values across monthly sheets, perfect for financial summaries.
Need More Than a Quick Fix in Excel?
If your team needs to work more confidently with formulas, summary sheets, reporting and real-world spreadsheets, we offer live instructor-led Excel training for businesses and individuals.
- Focused 90-minute Excel sessions for formulas, reporting and data clean-up
- Live online or onsite training tailored to the work your team actually does
- Practical help with SUMIF, XLOOKUP, PivotTables, Power Query and more
Step-by-Step Instructions for SUMIF Across Multiple Sheets
1. Start with SUMIF on One Sheet
- Select the cell for the result and type
=SUMIF, then specify the range, criteria (e.g., "consumables"), and sum range.
2. Set Up Monthly Sheet Names
- Create a list of monthly sheet names (April to March) on your sheet, ensuring they match the sheet tabs exactly.
3. Use SUMPRODUCT with INDIRECT
- Type
=SUMPRODUCT(SUMIF(INDIRECT(...))and reference each monthly sheet dynamically with INDIRECT to sum across sheets.
4. Complete the Formula
- Close the INDIRECT and SUMIF references, then press Enter. The formula will sum the specified values across all monthly sheets.
Final Tips on SUMIF Across Sheets
Using SUMIF across multiple sheets in Excel is a powerful way to summarize data from different tabs. Combining SUMPRODUCT and INDIRECT allows you to set up formulas that dynamically adjust as sheets or data change, ideal for creating yearly or project-based summaries.
Want Help Applying This in Your Workplace?
We can turn topics like SUMIF across sheets into practical training for your team, whether you need a short refresher session or a wider Excel course built around your files and reporting tasks.