You won’t believe the wonders you can achieve with the use of Named Ranges in Sumif in Google Sheets. Allow me to shed some light on this powerful feature and show you how it can revolutionize your conditional summing.
A Quick Introduction to Sumif in Google Sheets
Before we dive into the magic of Named Ranges, let’s take a quick look at the Sumif function. In case you’re unfamiliar with it, Sumif is a versatile function in Google Sheets that allows you to sum values based on specific criteria. If you want a more in-depth guide, feel free to use the search function on this website to find detailed tutorials on Sumif.
To give you a taste, here’s the basic syntax of the Sumif function:
SUMIF(range, criterion, [sum_range])And here’s an example to illustrate how it works:
=SUMIF(A3:A8, "Road Base", C3:C8)In this example, the formula checks the range A3:A8 for the criterion “Road Base” and sums the corresponding values in the sum_range.
Replacing Sum_range with Named Ranges
Now, let’s get into the juicy part – using Named Ranges to replace the sum_range in Sumif. Trust me, this can make your life so much easier!
In the sample data provided, we have three columns with numbers: C, D, and E. You can conditionally sum any of these columns using Sumif in Google Sheets. But here’s the cool part: you can replace the sum_range with a Named Range and make your formulas more dynamic.
To demonstrate this, let’s take a look at the animated gif below:
In this example, the drop-down menu in cell H2 contains three options: project1, project2, and project3. Each of these options represents a Named Range that corresponds to C3:C, D3:D, and E3:E, respectively.
To create this drop-down menu, simply select cell H2 and go to Data > Data Validation. Configure the data validation settings as shown in the image below:
Now, let’s take a look at the Sumif formula in cell I2. Instead of using a specific sum_range, we can use a Named Range from cell H2 dynamically:
=SUMIF(A3:A8, "Road Base", INDIRECT(H2))Note that we’ve used the INDIRECT function in this formula to interpret the Named Range from cell H2 correctly. Without it, the formula would treat cell H2 as a plain text string, not a reference to a Named Range.
Alternatively, you can directly use the Named Range as the sum_range in Sumif, like this:
=SUMIF(A3:A8, "Road Base", project1)However, if you want the flexibility of choosing the sum_range from a drop-down menu (like in our example), using INDIRECT is the way to go.
Replacing Criteria with Named Ranges
While replacing the sum_range with Named Ranges can be super handy, replacing the criteria might not be as useful in most cases. Nonetheless, I’ll show you how to do it, just in case you find it helpful.
Let’s say we want to replace the criteria in a Sumif formula with a Named Range. In the sample data, we’ll name the entire column G (G3:G) as “criteria”.
To do this, simply type “criteria” in cell G2 to label the column that contains the criteria. Here’s an image to illustrate:
Now, here’s how you can use the Named Range “criteria” in a Sumif formula:
=ArrayFormula(SUM(SUMIF(A3:A, Sheet2!criteria, C3:C15)))In this formula, we’re using ArrayFormula to sum the multiple outputs returned by Sumif. When there are multiple criteria involved, ArrayFormula is a must.
Replacing the Argument ‘Range’ with Named Ranges
Lastly, let’s explore how we can replace the ‘range’ argument in Sumif with Named Ranges. While we could use a drop-down menu for this too, I’ll show you the simpler method using just Named Ranges.
To begin, create two Named Ranges: “item” for A3:A and “status” for B3:B. Now, you can use these Named Ranges in your Sumif formula, like this:
=SUMIF(item, "Road Base", status)If you want to use a drop-down menu to switch the ‘range’, you can definitely do that as well. But for now, let’s keep it simple.
And there you have it! You now have a solid understanding of how to leverage the power of Named Ranges in Sumif with Google Sheets. These techniques will undoubtedly make your formulas more dynamic and efficient.
For more insights and tips on Google Sheets, visit Crawlan.com – your go-to resource for all things Google Sheets. Happy spreadsheeting!
