How to Use SUMIF From Another Sheet in Excel

This guide will explain how to use SUMIF from another sheet in Excel.

You may want to use the SUMIF function on a range outside the current sheet. 

Excel allows you to use data from outside your current sheet using external references.

An external reference refers to a cell or range of cells outside the current worksheet. Similar to regular cell references, the value returned will also change if the referenced cell changes in value.

Let’s take a look at a quick example of a use case where you might need to use an external reference with the SUMIF formula.

Suppose you have two sheets in your worksheet. The first worksheet is named ‘Data’, and the second worksheet is named ‘Summary’.

The Data sheet includes a list of purchases. Each purchase has an associated amount and a category it falls under. 

We want to set up our summary sheet to provide a breakdown of the total purchase amount of each category. How can we do this in Excel?

Since we want to find the total of a range given certain conditions, we will use the SUMIF function. We will write our formula in the summary sheet and use an external reference to retrieve values from the data tab.

We’ll explain how to use external references in the following section. We will tackle how to reference either an individual cell or a range of cells, and how to create a reference to another Excel workbook.

Now that we know when to use external references with the SUMIF function, let’s learn how to use it on an actual sample spreadsheet.

 

 

A Real Example of Using SUMIF from Another Sheet in Excel

The following section provides several examples of how to use this function. We will also go into detail about the formulas and tools used in these examples.

First, let’s take a look at our sample dataset. We have a table with two fields: category and amount. 

sample dataset

 

This table can be found in the first sheet in our workbook. We’ve labeled this sheet ‘Data’ for convenience.

In the ‘Summary’ sheet, we’ve created another table to summarize the total amount purchased for each category.

use SUMIFS in another sheet to summarize sample dataset

 

To get the values in Column C, we just need to use the following formula:

=SUMIF(Data!$A$2:$A$17,Summary!B5,Data!$B$2:$B$17)

 

Creating External References

Let’s try to understand how the formula above works.

We can create an external reference to a cell in another sheet in the current workbook by using the following syntax:

Sheet_name!Cell_address

If we want to reference a range of cells, we’ll use the following syntax:

Sheet_name!First_cell:Last_Cell

If the worksheet name has spaces or non-alphabetical characters, you must enclose the name in single quotation marks. For example, if we want to reference cell A1 in a sheet named ‘July 2022’, we’ll write it like this:

=’July 2022’!A1

What should we do when we must reference cells in another workbook?

If the source workbook is open, we can add the workbook name in square brackets. 

Suppose we have a workbook with the filename ‘Annual_Report.xlsx’. We want to retrieve the range B1:B100 in the sheet labeled ‘January’. 

We can write this external reference as follows:

[Annual_Report.xlsx]January!B1:B100 

If the source workbook is closed, we will have to indicate the entire path to the source workbook.

C:\Work\Reports\[Annual+Report.xlsx]January!B1:B100 

Do you want to take a closer look at our examples? You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try using SUMIF from another sheet in Excel, head over to the next section to read our step-by-step breakdown on how to do it!

 

 

How to Use SUMIF From Another Sheet in Excel

This section will guide you through each step needed to start using SUMIF from another sheet in Excel.

Follow these steps to start using external references with the SUMIF function:

  1. First, select the cell where you want to add the SUMIF formula. In this example, we will start with cell C5 in the Summary sheet.
    in new sheet, SUMIF from another sheet in Excel
  2. Next, type ‘=SUMIF(‘ in the formula bar to start the SUMIF function.
    use SUMIF from another sheet in Excel
  3. The first argument of SUMIF will determine the range Excel will check to see if the amount should be included in the sum. Since this range is located on another sheet, we must type the sheet name followed by the ‘!’ symbol.
    type name of spreadsheet followed by !
  4. Write the cell range you want to reference after the ‘!’ symbol.
    add cell range
  5. Next, we’ll use the same syntax to add a reference to the second argument. The second argument will indicate what criteria to use for the SUMIF function. In this example, we want to only add cells with the category ‘A’.
    SUMIF from another sheet in Excel
  6. We’ll add another external reference for the third argument of the SUMIF function. This argument will determine the range of values that SUMIF will use to calculate the sum.
    add another external reference
  7. Type the Enter key to evaluate the function.
    evaluate formula
  8. Use the Fill Handle tool to fill out the rest of the summary table. We’ve converted our external references to absolute references to prevent the ranges from changing.
    SUMIF from another sheet in Excel

 

These are all the steps needed to start using SUMIF from another sheet in Excel.

 

 

This step-by-step guide should provide you with all the information you need to use the SUMIF function on a range of values in another sheet.

We’ve explained in detail how to create an external reference you can use with the SUMIF function.

The SUMIF function is just one example of the many Excel functions you can use in your spreadsheets. Our website offers hundreds of other functions and methods to help you get more out of Microsoft Excel.

With so many other Excel functions available, you can find one appropriate for your use case.

Don’t miss out on our team’s new spreadsheet tips, tricks, and best practices. Subscribe to our newsletter to stay updated on the latest guides from us!

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like