You won’t believe the wonders you can do with Named Ranges in SUMIF in Google Sheets! Let me shed some light on how to use Named Ranges in SUMIF, the conditional sum function.
The SUMIF function has the following arguments: range, criterion, and sum_range. You can replace any of these arguments with Named Ranges to simplify and enhance your formulas.
The role of Named Ranges in SUMIF in Google Sheets varies depending on which argument you’re using them in.
Before we begin, here’s a quick introduction to the SUMIF function.
Introduction to SUMIF
I believe it’s important to first introduce you to the SUMIF function before diving into the use of Named Ranges within it.
I’ve covered SUMIF extensively on this site. You can find an impressive set of guides by clicking on the search icon in the top navigation bar and typing “SUMIF.” However, to save you time, here’s a quick tutorial on how to use SUMIF in Google Sheets.
Syntax:
SUMIF(range, criterion, [sum_range])Example:
Sample Data: Please refer to the first screenshot below for the sample data used in this example.
=SUMIF(A3:A8, "Road Base", C3:C8)In short, this formula checks the range A3:A8 for the criterion “Road Base” and sums the corresponding values in C3:C8 where the condition is met.
Now, let’s explore how you can replace the range, criterion, and sum_range in SUMIF with Named Ranges.
Named Ranges in SUMIF – Replacing sum_range
I find Named Ranges particularly useful when replacing the sum_range in SUMIF, so let’s start with that.
In my sample data, there are three columns with numbers: Column C, D, and E. You can conditionally sum any of these columns using SUMIF in Google Sheets.
Here’s a helpful tip for using Named Ranges in SUMIF: you can switch the sum_range using a drop-down menu!
Example
The drop-down menu in cell H2 contains three options: project1, project2, and project3. These correspond to the Named Ranges for C3:C8, D3:D8, and E3:E8, respectively.
Note:
- You can create Named Ranges by selecting the cells you want to name and going to the menu: Data > Named range.
- To create a drop-down menu, go to Insert > Drop-down.
To create the drop-down menu for the sum_range, select cell H2 and apply the following data validation settings.
Here is the SUMIF formula in cell I2, demonstrating the use of Named Ranges in SUMIF:
=SUMIF(A3:A8, "Road Base", INDIRECT(H2))In this formula, I’ve replaced the sum_range with the Named Ranges in cell H2. The INDIRECT function is used here, which I’ll explain next.
The INDIRECT function is necessary because, without it, the formula would treat the value in cell H2 as a text string rather than as a reference to the Named Ranges. This allows us to dynamically switch the sum_range in the SUMIF function using the drop-down list of Named Ranges.
Named Ranges in SUMIF – Replacing the Criteria
Although I don’t find this usage as useful, I will show you how to replace the criteria in SUMIF with a Named Range in Google Sheets.
I’m going to name an entire column (G3:G) as “criteria.” Here, “criteria” is the name of the Named Range.
In cell G2, type the field name as “criteria” so you can easily identify the column containing the criteria later.
You can’t use SUMIF like this:
=SUMIF(A3:A, criteria, C3:C15)Since there are multiple criteria in the column G3:G (Named Range “criteria”), you should use the formula as shown below:
=ARRAYFORMULA(SUM(SUMIF(A3:A15, criteria, C3:C15)))The SUM function adds up the multiple outputs returned by SUMIF. When using multiple criteria in SUMIF, the ARRAYFORMULA is required.
Named Ranges in SUMIF – Replacing the Argument ‘Range’
Just like with sum_range, you can use a drop-down menu to switch the range in SUMIF. However, I’ll just show you how to replace the range with a Named Range.
Here’s how to replace the range in SUMIF with Named Ranges in Google Sheets. First, create two Named Ranges:
itemforA3:A15statusforB3:B15
Now, use the following formula (you can also use a drop-down if you prefer):
=SUMIF(item, "Sand", C3:C15)
This demonstrates how to replace the range in SUMIF with a Named Range.
