Mastering SUMIF with Named Ranges in Google Sheets

Published on

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!

Replacing sum_range with Named Ranges in SUMIF

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:

  1. You can create Named Ranges by selecting the cells you want to name and going to the menu: Data > Named range.
  2. 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.

Drop-down list with Named Range in Google Sheets

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.

Replacing criteria with Named Range in SUMIF

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:

  1. item for A3:A15
  2. status for B3:B15

Now, use the following formula (you can also use a drop-down if you prefer):

=SUMIF(item, "Sand", C3:C15)
Replacing range with Named Ranges in SUMIF

This demonstrates how to replace the range in SUMIF with a Named Range.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

How to Extract the Last N Non-Blank Rows in Excel Dynamically

You can use the following formula to extract the last N non-blank rows in...

Count Consecutive Duplicates in Excel (Dynamic Array Formula)

Counting consecutive duplicates in Excel is useful for analyzing patterns, detecting repetitive sequences, and...

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

More like this

How to Break RANK Ties Alphabetically in Google Sheets

The RANK function in Google Sheets is commonly used to assign rankings to numerical...

Google Sheets: Highlight an Entire Column If Any Cell Has an Error

Google Sheets allows you to highlight an entire column if any cell has an...

Google Sheets: Extract Top N per Group from Query Aggregation

When working with grouped and aggregated data in Google Sheets, you might need to...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.