Google Sheets: How to Sum a Column Based on Multiple Conditions

In Google Sheets, you can use formulas like SUMIF, SUMIFS, DSUM, SUMPRODUCT, or QUERY to sum a column based on multiple conditions. This topic tests your ability to apply logical AND and OR conditions within formulas. In my opinion, QUERY is the most versatile and flexible formula for this task.

My goal is to equip you with various options to sum a column based on multiple conditions. Let’s dive in!

Sample Dataset

The dataset used for all the examples includes four columns: Column A contains customer names, Column B lists the outstanding amounts, Column C provides the aging details, and Column D specifies the follow-up priority.

Here’s how the data looks:

Customer	Outstanding Amount	Aging	Priority
Info Inspired	5000	90	High
Info Inspired	4000	90	High
ABC Co	1000	60	Moderate
ABC Co	2500	60	Moderate
ABC Co	1500	20	Moderate
XYZ Co	15000	20	Low
XYZ Co	12000	20	Low
XYZ Co	10000	20	Low
Info Inspired	1000	90	Moderate

We will use this dataset throughout this tutorial to demonstrate how to sum Column B based on multiple conditions in other columns. I recommend you create this dataset in your sheet so you can test the formulas as we go along.

Single Criterion

Problem: Sum “Outstanding Amount” in Column B where “Customer” in Column A is “Info Inspired.”

QUERY Formula

=QUERY(A1:D, "SELECT SUM(B) WHERE A='Info Inspired' LABEL SUM(B)''", 1)

This formula is case-sensitive. To make it case-insensitive, use the following:

=QUERY(A1:D, "SELECT SUM(B) WHERE LOWER(A)='info inspired' LABEL SUM(B)''", 1)

Remember, all the QUERY formulas below are case-sensitive unless explicitly adjusted for case insensitivity.

Syntax: QUERY(data, query, [headers])

SUMIF Formula

=SUMIF(A2:A, "Info Inspired", B2:B)

Syntax: SUMIF(range, criterion, [sum_range])

SUMIFS Formula

=SUMIFS(B2:B, A2:A, "Info Inspired")

Syntax: SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, …])

SUMPRODUCT Formula

=SUMPRODUCT(A2:A="Info Inspired",  B2:B)

The expression A2:A="Info Inspired" evaluates to an array of TRUE or FALSE values, which serves as array1. Within this array, TRUE is converted to 1 and FALSE to 0. The formula then multiplies these values by the corresponding entries in array2 (B2:B), effectively calculating a conditional sum.

Syntax: SUMPRODUCT(array1, [array2, …])

Note for Excel Users: In Excel, the above SUMPRODUCT formula may not work as expected. Instead, you need to use a variation that explicitly multiplies the arrays, like this:

=SUMPRODUCT((A2:A="Info Inspired") * B2:B)

This difference also applies to the other SUMPRODUCT formulas discussed below.

DSUM Formula

In E1: Type Customer.
In E2: Type Info Inspired.
Use this formula:

=DSUM(A1:D, 2, E1:E2)

2 refers to the second column (Outstanding Amount).

Syntax: DSUM(database, field, criteria)

That’s how you sum a column based on a single condition. Next, we’ll explore how these formulas evolve to handle multiple conditions effectively.

Multiple Conditions: OR Logic

Problem: Sum “Outstanding Amount” in Column B if “Priority” in Column D is “High” or “Moderate.”

QUERY Formula

=QUERY(A1:D, "SELECT SUM(B) WHERE D='High' OR D='Moderate' LABEL SUM(B)'' ", 1)

SUMIF Formula with ArrayFormula

=ArrayFormula(SUMIF((D2:D="High") + (D2:D="Moderate"), 1, B2:B))

(D2:D="High") + (D2:D="Moderate") creates an array of 1s (matching rows) and 0s (non-matching rows).

SUMIFS Formula with ArrayFormula

=ArrayFormula(SUMIFS(B2:B, (D2:D="High") + (D2:D="Moderate"), 1))

This formula applies similar logic to SUMIF. It sums the values in B2:B where the follow-up priority in column D is either “High” or “Moderate”.

SUMPRODUCT Formula

=SUMPRODUCT((D2:D="High") + (D2:D="Moderate"), B2:B)

No need for ARRAYFORMULA, as SUMPRODUCT inherently processes arrays.

DSUM Formula

In E1: Type Priority.
In E2: Type High.
In E3: Type Moderate.
Use this formula:

=DSUM(A1:D, 2, E1:E3)

Multiple Conditions: AND Logic

Problem: Sum “Outstanding Amount” in Column B if “Customer” is “Info Inspired” and “Aging” is 90.

QUERY Formula

=QUERY(A1:D, "SELECT SUM(B) WHERE A='Info Inspired' AND C=90 LABEL SUM(B)''", 1)

SUMIFS Formula

=SUMIFS(B2:B, A2:A, "Info Inspired", C2:C, 90)

SUMPRODUCT Formula

=SUMPRODUCT(A2:A="Info Inspired", C2:C=90, B2:B)

DSUM Formula

In E1: Type Customer.
In E2: Type Info Inspired.
In F1: Type Aging.
In F2: Type 90.
Use this formula:

=DSUM(A1:D, 2, E1:F2)

Combined AND & OR Logic

Problem: Sum “Outstanding Amount” in Column B if “Customer” is “Info Inspired” and “Priority” is “High” or “Moderate.”

QUERY Formula

=QUERY(A1:D, "SELECT SUM(B) WHERE A='Info Inspired' AND (D='High' OR D='Moderate') LABEL SUM(B)''", 1)

In this formula, note how explicit precedence is defined using parentheses. The condition (D='High' OR D='Moderate') ensures that the logical OR operates only on values in Column D, while AND applies to the overall conditions involving Column A and Column D.

This structure is crucial for correctly interpreting and executing the query logic.

SUMIFS Formula with ArrayFormula

=ArrayFormula(SUMIFS(B2:B, A2:A, "Info Inspired", (D2:D="High") + (D2:D="Moderate"), 1))

SUMPRODUCT Formula

=SUMPRODUCT(A2:A="Info Inspired", ((D2:D="High") + (D2:D="Moderate")), B2:B)

DSUM Formula

In E1: Type Customer.
In E2: Type Info Inspired.
In E3: Type Info Inspired.
In F1: Type Priority.
In F2: Type High.
In F3: Type Moderate.
Use this formula:

=DSUM(A1:D, 2, E1:F3)

Conclusion

We explored multiple ways to sum a column based on single or multiple conditions in Google Sheets. While DSUM is excellent for structured data, QUERY stands out for its flexibility. Other formulas like SUMIF, SUMIFS, and SUMPRODUCT are effective but may require creative workarounds for complex conditions.

For date-based or comparison criteria, refer to my function guide on handling such conditions in these formulas.