How to Sum Data Across Multiple Sheets in Excel Using SUMIFS

Ever find yourself in a situation where you need to sum data from one sheet based on criteria in another? You’re not alone. This is a common problem that many Excel users face, especially when working with complex datasets spread across multiple sheets.

Why This Problem Happens

The challenge of summing data across different sheets arises due to the need for more flexible and conditional aggregations. Excel’s SUMIFS function is powerful but can be confusing when referencing data across sheets.

Step-by-Step Solution

Real-World Example 1: Summing Sales by Region

Let’s say you have two sheets – “Sales Data” and “Summary”. Your goal is to sum sales amounts in “Sales Data” based on a specific region mentioned in “Summary”.

1. Sheet 1 (“Sales Data”)
   

   Region	Amount
   North	500
   South	700
   East	600
   West	800

2. Sheet 2 (“Summary”)
   

   Region	Total Sales
   North	[Formula Here]
   South	[Formula Here]
   East	[Formula Here]
   West	[Formula Here]

The formula to sum sales for the North region in “Summary” would look like this:

=SUMIFS('Sales Data'!B:B, 'Sales Data'!A:A, "North")

Real-World Example 2: Calculating Project Hours

In another scenario, you have two sheets – “Project Hours” and “Summary”. You want to sum the total hours worked by a specific employee.

1. Sheet 1 (“Project Hours”)
   

   Employee	Hours
   Alice	8
   Bob	7
   Alice	4
   Charlie	9

2. Sheet 2 (“Summary”)
   

   Employee	Total Hours
   Alice	[Formula Here]
   Bob	[Formula Here]
   Charlie	[Formula Here]

The formula to sum hours worked by Alice in “Summary” would look like this:

=SUMIFS('Project Hours'!B:B, 'Project Hours'!A:A, "Alice")

Real-World Example 3: Summing Data with Multiple Criteria

You have two sheets – “Expense Reports” and “Summary”. You need to sum expenses for a specific department during a particular month.

1. Sheet 1 (“Expense Reports”)
   

   Department	Month	Amount
   HR	January	200
   Engineering	January	150
   HR	February	300
   Engineering	February	400

2. Sheet 2 (“Summary”)
   

   Department	Month	Total Amount
   HR	January	[Formula Here]
   Engineering	February	[Formula Here]

The formula to sum expenses for HR in January would look like this:

=SUMIFS('Expense Reports'!C:C, 'Expense Reports'!A:A, "HR", 'Expense Reports'!B:B, "January")

Advanced Variation: Using SUMIFS with Multiple Criteria Ranges

Sometimes you need to sum values based on criteria in multiple columns. Let’s extend our previous example by adding another criterion.

1. Sheet 1 (“Expense Reports”)
   

   Department	Month	Project	Amount
   HR	January	Payroll	200
   Engineering	January	Development	150
   HR	February	Training	300
   Engineering	February	Testing	400

2. Sheet 2 (“Summary”)
   

   Department	Month	Project	Total Amount
   HR	January	Payroll	[Formula Here]
   Engineering	February	Testing	[Formula Here]

The formula to sum expenses for HR in January under the Payroll project would look like this:

=SUMIFS('Expense Reports'!D:D, 'Expense Reports'!A:A, "HR", 'Expense Reports'!B:B, "January", 'Expense Reports'!C:C, "Payroll")

Common Mistakes or Misconceptions

1. Incorrect Cell References: Ensure that you are using the correct sheet names and cell references in your SUMIFS formula.
2. Absolute vs Relative References: Be cautious with the use of absolute ($) and relative references. This can affect how formulas update when copied to other cells.
3. Ignoring Case Sensitivity: The SUMIFS function is not case-sensitive, so “North” and “north” are treated as the same.

Optional VBA Version for Summing Data Across Sheets

If you prefer to use VBA for more complex or dynamic scenarios, here’s a simple macro that sums data based on criteria:

Sub SumDataAcrossSheets()
    Dim wsSummary As Worksheet
    Dim wsData As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim sumAmount As Double

    ' Set worksheet references
    Set wsSummary = ThisWorkbook.Sheets("Summary")
    Set wsData = ThisWorkbook.Sheets("Sales Data")

    ' Find the last row in the data sheet
    lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

    ' Loop through each row in the data sheet and sum values based on criteria
    For i = 2 To lastRow
        If wsData.Cells(i, 1).Value = "North" Then
            sumAmount = sumAmount + wsData.Cells(i, 2).Value
        End If
    Next i

    ' Output the result to the Summary sheet
    wsSummary.Range("B2").Value = sumAmount
End Sub

Conclusion

Summing data across multiple sheets in Excel is a common task that can be efficiently managed with the SUMIFS function. By understanding the basic syntax and applying it to real-world examples, you can streamline your data analysis processes.

For more advanced features and automation, consider exploring tools like CelTools, which offers a suite of additional Excel features for auditing, formulas, and automation.

Written By: Ada Codewell – AI Specialist & Software Engineer at Gray Technical