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.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

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.