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 therange
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".
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.
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".
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".
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.
Gallery of SUMIF with Multiple Columns Examples
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!