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.

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

dataset

Download Practice Workbook

Three Methods to Use Sumif Across Multiple Sheets

Method 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,

=SUMIF('Quarter 1'!B5:B9,'Sales Summary'!B5,'Quarter 1'!C5:C9)+SUMIF('Quarter 2'!B5:B9,'Sales Summary'!B5,'Quarter 2'!C5:C9)+SUMIF('Quarter 3'!B5:B9,'Sales Summary'!B5,'Quarter 3'!C5:C9)

 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.

sumif across multiple sheet

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

sumif value

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

sumif values for all cell

Read More: SUMIF for Multiple Criteria Across Different Sheets in Excel (3 Methods)

Method 2: Using SUMPRODUCT SUMIF and INDIRECT Function

Without repeating the SUMIF function multiple times, you can use the SUMPRODUCT function, the SUMIF function, and the INDIRECT function altogether 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.

sheet name

After that, Type the following formula in cell C5,

=SUMPRODUCT(SUMIF(INDIRECT("'"&$E$5:$E$7&"'!$B$5:$B$9"),B5,INDIRECT("'"&$E$5:$E$7&"'!$C$5:$C$9")))

 Here, $E$5:$E$7 refers to different sheets for the values of quarterly sales.

          B$5:$B$9 = lookup range for criteria

          B5 is the criteria (Mark)

          $C$5:$C$9 = range for value if criteria match.

sumif across multiple sheets

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

sumif values

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

sumif values for all cell


Similar Readings


Method 3: Using VBA to Sum 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 the 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.

vba

A code window will appear.

code window

Copy and paste the following code in 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

code in vba

After that close the VBA window and type the following formula in cell C5,

=SUMIFOS(B5,'Quarter 1'!B5:B9,'Quarter 1'!C5:C9,'Quarter 2'!B5:B9,'Quarter 2'!C5:C9,'Quarter 3'!B5:B9,'Quarter 3'!C5:C9)

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.

sumif across multiple sheets

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

sumif across mutiple sheets

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

sumif in all cells

Read More: SUMIF Multiple Ranges [6 Useful Ways]

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

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo