Summing Multiple Columns with SUMIF in Google Sheets

We can use the SUMIF function to sum multiple columns based on a criterion in Google Sheets. To achieve this, we will use a clever method in the SUMIF range part.

The SUMIF function is for summing a column based on a criterion in another column or range.

It follows the syntax SUMIF(range, criterion, [sum_range]), where range and sum_range can be 2D arrays of equal size. We will utilize this flexibility of SUMIF to sum multiple columns in Google Sheets.

Let’s start with a regular use of the SUMIF function in Google Sheets.

Sample Data:

DescriptionJanuaryFebruary
Lemon100100
Lemon500200
Avocado400100
Watermelon500200

The sample data consists of fruit names in A1:A5, their production quantities in January in B1:B5, and February in C1:C5.

We can use the following formula to sum the total production quantities of Lemon in January:

=SUMIF(A2:A5, "Lemon", B2:B5)

Where:

  • range: A2:A5
  • criterion: “Lemon”
  • sum_range: B2:B5

To get the quantities in February, we can replace B2:B5 in the above formula with C2:C5.

How do we get the total production quantities of Lemon in both January and February using a single SUMIF formula? I mean, how can we use SUMIF with multiple sum columns?

Let’s explore that simple trick.

SUMIF with Multiple Sum Columns

Here is a clever method to include more than one sum column in the SUMIF function in Google Sheets.

Steps:

  • Determine the number of columns to sum.
  • Duplicate the range according to the number of sum columns. For example, if there are 2 columns, duplicate the range 2 times (Syntax – {range, range, ...}).
  • Use the multiple sum columns as the sum_range.

Note: If you have several sum columns, the above method may not be practical. I’ll share a dynamic approach after this.

Example:

Based on our above ‘fruit’ data, the range is A2:A5, and the number of columns to add is two. So, we must duplicate the range A2:A5 twice. Here’s how:

{A2:A5, A2:A5}

Now see the SUMIF formula:

=SUMIF({A2:A5, A2:A5}, "Lemon", B2:C5)

This formula would return 900 as the output.

This method allows us to include multiple sum columns in the SUMIF function. This approach also works with Array Formulas (multiple criteria) in SUMIF. Just ensure you enter the formula as an array formula. Here is an example:

=ArrayFormula(SUMIF({A2:A5, A2:A5}, E2:E3, B2:C5))
Example of using SUMIF with multiple sum columns

Since E2:E5 contains the fruit names ‘Lemon’ and ‘Watermelon’ (two criteria), the formula returns two values.

Have Several Sum Columns? Here’s a Cool Hack to Repeat the Range

When you have several sum columns to include in the SUMIF, the above array approach may not be practical.

For example, if you have 12 columns from January to December as sum_range and one range, you would need to use the range as {range, range, range, range, range, range, range, range, range, range, range, range}.

This can make the formula cumbersome and difficult to manage. Here is a solution to this problem:

Instead of specifying the array as above, you can use this formula:

VLOOKUP(range, range, 1, SEQUENCE(1, n, 0, 0))

This VLOOKUP formula will repeat the range as many times as you want. You just replace ‘n’ with the number of times to repeat the range.

When using this formula, you should enter the SUMIF as an array formula, regardless of the number of criteria you use.

For example, we can replace our above SUMIF two columns formula with the following one:

=ArrayFormula(SUMIF(VLOOKUP(A2:A5, A2:A5, 1, SEQUENCE(1, 2, 0, 0)), E2:E3, B2:C5))

Where:

range: SUMIF(VLOOKUP(A2:A5, A2:A5, 1, SEQUENCE(1, 2, 0, 0))
criterion: E2:E3
sum_range: B2:C5

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.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.