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.
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.
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:
- First, select the cell where you want to add the
SUMIFformula. In this example, we will start with cell C5 in the Summary sheet.
- Next, type ‘=SUMIF(‘ in the formula bar to start the
SUMIFfunction.
- The first argument of
SUMIFwill 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.
- Write the cell range you want to reference after the ‘!’ symbol.
- 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
SUMIFfunction. In this example, we want to only add cells with the category ‘A’.
- We’ll add another external reference for the third argument of the
SUMIFfunction. This argument will determine the range of values thatSUMIFwill use to calculate the sum.
- Type the Enter key to evaluate the function.
- 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.
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!
