stats

5 Ways To Use Sumif With Multiple Columns In Google Sheets

Intro

The SUMIF function in Google Sheets is a powerful tool for summarizing data based on specific conditions. While it's commonly used with a single column, there are situations where you need to use it with multiple columns. In this article, we'll explore five ways to use SUMIF with multiple columns in Google Sheets.

What is SUMIF?

Before we dive into using SUMIF with multiple columns, let's quickly review what the function does. SUMIF is a Google Sheets function that allows you to sum a range of cells based on a specific condition. The syntax for SUMIF is:

SUMIF(range, criterion, [sum_range])

  • range is the range of cells that you want to apply the criterion to.
  • criterion is the condition that you want to apply to the range.
  • [sum_range] is the range of cells that you want to sum. If you omit this argument, the function will sum the values in the range argument.

Method 1: Using Multiple Columns with the SUMIFS Function

Google Sheets has a built-in function called SUMIFS that allows you to sum a range of cells based on multiple conditions. The syntax for SUMIFS is:

SUMIFS(sum_range, range1, criterion1, [range2], [criterion2],...)

In this example, we'll use SUMIFS to sum the values in column C based on conditions in columns A and B.

A B C
North Sales 100
North Sales 200
South Sales 50
South Sales 75

Formula: =SUMIFS(C:C, A:A, "North", B:B, "Sales")

This formula will sum the values in column C only for the rows where the value in column A is "North" and the value in column B is "Sales".

SUMIFS Function Example

Method 2: Using the FILTER Function with SUM

Another way to use SUMIF with multiple columns is by combining the FILTER function with SUM. The FILTER function allows you to filter a range of cells based on a specific condition.

Formula: =SUM(FILTER(C:C, (A:A = "North") * (B:B = "Sales")))

This formula will filter the values in column C only for the rows where the value in column A is "North" and the value in column B is "Sales", and then sum the resulting values.

FILTER Function Example

Method 3: Using the QUERY Function

The QUERY function is a powerful tool in Google Sheets that allows you to perform complex queries on your data.

Formula: =QUERY(A:C, "SELECT SUM(C) WHERE A = 'North' AND B = 'Sales'")

This formula will sum the values in column C only for the rows where the value in column A is "North" and the value in column B is "Sales".

QUERY Function Example

Method 4: Using Multiple Columns with the SUMIF Function and Array Formula

You can also use the SUMIF function with multiple columns by using an array formula.

Formula: =SUM(IF((A:A = "North") * (B:B = "Sales"), C:C, 0))

This formula will sum the values in column C only for the rows where the value in column A is "North" and the value in column B is "Sales".

Array Formula Example

Method 5: Using Multiple Columns with the SUMIF Function and Helper Columns

Another way to use SUMIF with multiple columns is by creating helper columns that contain the conditions you want to apply.

A B C D E
North Sales 100 TRUE FALSE
North Sales 200 TRUE FALSE
South Sales 50 FALSE TRUE
South Sales 75 FALSE TRUE

Formula: =SUMIF(D:D, TRUE, C:C)

This formula will sum the values in column C only for the rows where the value in column D is TRUE.

Helper Columns Example

Gallery of SUMIF with Multiple Columns Examples

We hope this article has helped you learn how to use SUMIF with multiple columns in Google Sheets. Whether you're using the SUMIFS function, the FILTER function, the QUERY function, an array formula, or helper columns, there's a method that's right for you. Do you have any questions about using SUMIF with multiple columns? Let us know in the comments below!

Jonny Richards

Love Minecraft, my world is there. At VALPO, you can save as a template and then reuse that template wherever you want.