SUMIF Across Multiple Sheets in Excel (3 Methods)

If you have data across multiple sheets, you may need to know some techniques to use the SUMIF function. In this article, I will introduce you to three methods by which you will be able to use SUMIF across multiple sheets in Excel. Before diving into the methods in the next section, take a look at the overview of this article.

SUMIF Across Multiple Sheets in Excel: 3 Methods

In our datasheet, we have Quarterly sales by different salesmen across different sheets. Now we want to calculate the yearly sales of different salesmen. For that, we have to sum up the different quarters’ sales of each salesman.

1. Using SUMIF Function for Each Sheet

One way to make the calculation is by using the SUMIF function for every sheet. Suppose, we want to calculate the yearly sales of each salesman in a sheet named Sales Summary. Type the following formula in cell C5.

 Here,
‘Quarter 1′!B5:B9’ = Range in sheet Quarter 1 where the criteria will be matched.
‘Sales Summary’!B5′ = Criteria.
‘Quarter 1′!C5:C9’ = Range in sheet Quarter 1 from where value for summation will be taken.

In a similar manner, SUMIF is used for all of the sheets.

After pressing ENTER, you will get the summation of all three quarters’ sales of Mark in cell C5.

Drag the sales C5 to the end of your dataset and you will get the yearly sales of all salesmen.

Read More: SUMIF for Multiple Criteria Across Different Sheet in Excel

2. Merging SUMPRODUCT SUMIF and INDIRECT Functions Across Multiple Sheets

Without repeating the SUMIF function multiple times, you can use the SUMPRODUCT, SUMIF, and INDIRECT functions together to get the same result. First, we will insert the name of the sheets (Quarter 1, Quarter 2, Quarter 3) in the sheet where we will make the calculation for yearly sales.

After that, type the following formula in cell C5.

• $E$5:$E$7 = refers to different sheets for the values of quarterly sales.
• B$5:$B$9 = lookup range for criteria.
• B5 = the criteria (Mark).
• $C$5:$C$9 = range for value if criteria match.

After pressing ENTER, you will get the summation of all three quarters’ sales of Mark in cell C5.

Drag the sales C5 to the end of your dataset and you will get the yearly sales of all salesmen.

Read More: SUMIF and VLOOKUP Functions Across Multiple Sheets in Excel

Similar Readings

• SUMIF with Multiple Criteria in Different Columns in Excel
• How to Sum Multiple Columns Based on Multiple Criteria in Excel

3. Utilizing VBA to Implement SUMIF Across Multiple Sheets

If you have a lot of sheets, the above two methods can be very time-consuming and complex. To make the calculation faster you can take the help of Visual Basic Applications (VBA) and make a custom formula.

First, press ALT+F11 to open the VBA window. Right-click on the sheet name and select Insert > Module.

A code window will appear. Copy and paste the following code into this window.

Function SUMIFOS(lookup_value As Range, ParamArray cellranges() As Variant)
Dim i As Integer, rng1 As Variant, temp As Single, a As Boolean
Dim rng2 As Variant, value As Variant, j As Single
If (UBound(cellranges) + 1) Mod 2 <> 0 Then
  Exit Function
End If
For i = LBound(cellranges) To UBound(cellranges) Step 2
    If cellranges(i).Rows.Count <> cellranges(i + 1).Rows.Count Then
    End If
    If cellranges(i).Columns.Count <> 1 Then
        Exit Function
End If
    rng1 = cellranges(i).value
    rng2 = cellranges(i + 1).value
    For j = LBound(rng1) To UBound(rng1)
        For Each value In lookup_value
            If UCase(rng1(j, 1)) = UCase(value) Then a = True
        Next value
        If a = True Then temp = temp + rng2(j, 1)
        a = False
    Next j
Next i
SUMIFOS = temp
End Function

Close the VBA window and type the following formula in cell C5.

Here, SUMIFOS is the custom function, B5 is the lookup value, Quarter 1′!C5:C9 is the range for value in the sheet named Quarter 1, and Quarter 1′!B5:B9 is the range for criteria in the sheet named Quarter 1. You can insert the value from as many sheets as you want in this formula.

After pressing ENTER, you will get the summation of all three quarters’ sales of Mark in cell C5.

Drag the sales C5 to the end of your dataset and you will get the yearly sales of all salesmen.

Read More: How to Apply SUMIF with Multiple Ranges in Excel

Download Practice Workbook

Conclusion

Using the first method is not very convenient for a very large amount of sheets because it will consume a lot of time. You can use method 1 if you have only a couple of sheets. But methods 2 and 3 will be efficient for a very large amount of sheets.

If you face any problem while using any of the methods to apply SUMIF across multiple sheets, please leave a comment.

Related Articles

• Sum Based on Column and Row Criteria in Excel