The above topic, Sum a Column based on multiple conditions in Google Sheets, tests your capability in using logical AND, OR in formulas. You can choose between Google Sheets SUMIF, SUMIFS or Query in multiple criteria SUM. In my opinion, Query is the easiest formula to sum a column based on conditions in multiple columns.
I’m not telling you that using SUMIF or SUMIFS is tough. If you just want to SUM, no matter the number of AND, OR conditions you want to include, you can also use SUMIF or SUMIFS. But with Query in Google Sheets, you can do more. No need to limit to SUM. With one Query formula, you can do the SUM as per your criteria and apply even more data manipulation.
In this Google Sheets tutorial, you can learn the use of AND, OR in Google Sheets Query as well as in SUMIF and SUMIFS. My main aim is to armor you with different options to sum a column based on multiple conditions. You can find the best tips to use multiple Criteria SUMIF / SUMIFS / QUERY in this Google Spreadsheet tutorial.
The following is the screenshot of the sample dataset that I’m going to use in the formulas below. You can either enter the data in your Google Sheets or just read on. If you create this data set, keep the column and row positions as it is.
Let’s begin our experiment with this sample data. Please just ignore the color highlighting.
Google Sheets Query Formula to Sum a Column Based on Multiple Conditions
I am not going to limit my formula to one specific condition. Your requirements may be different. So I’m considering different scenarios here and the formula may vary accordingly. After completing this Google Sheets tutorial, I hope you can handle any number of criteria in Query and as well as in SUMIF / SUMIFS.
I highly recommend you to read the below two posts. The reason, the number, text, date, and usage of comparison operators are different in SUMIF / SUMIFS and QUERY. Further, I am only using number and text as criteria in the below examples. So if you want to know how to use all types of criteria in SUMIF and Query, the below spreadsheet tutorials can come in handy for you.
1. Usage of Different Criterion in Google Sheets SUMIFS
2. Date as Criteria in Query – The Long-winded Approach in Google Sheets
3. Date Criteria in Query Function in Google Sheets
Having said that I’m taking you to the SUMIF / SUMIFS / Query formula section of my tutorial.
1. Google Sheets Formula to Sum a Column Based on Condition in Another Column
Please check my above sample data as this formula is based on that. For your quick reference, I’ve just cut and pasted the field labels here and also in each section below. I think I should make it clear few more things before jumping to my first formula.
What is the field label in Google Sheets?
Filed labels are column names. In our example, Customer, Outstanding Amount, Ageing, and Priority are the so-called field labels aka column names.
Must Read: Column Heading | Column Label | Column Name | Field | Field Label in Google Sheets
Formula:
Here I am going to sum a column based on a single condition in another column.
Here I want to Sum “Outstanding Amount” in Column B if conditions in Column A is “Info Inspired”.
=query(A1:D10,"Select Sum (B) where A='Info Inspired'")
This formula sums column B if the value in Column A is “Info Inspired”
Here is the SUMIF alternative to summing a column based on a condition in another column.
=sumif(A2:A10,"Info Inspired",B2:B10)
Please compare both the formulas. In doing so, there is one benefit. If you know either of the formulas, you can learn the other one.
2. Google Sheets Formula to Sum a Column Based on Multiple Conditions in Another Column – OR Logic
Here you can learn to use the OR Logic in Query and SUMIF.
How to sum column B if the values in Column D is either “High” or “Moderate”
=query(A1:D10,"Select Sum (B) where D='High' or D='Moderate'")
See how to use multiple criteria column in SUMIF. It’s a kind of array use.
=ArrayFormula(sum(sumif(D2:D10,{"High";"Moderate"},B2:B10)))
See the detailed use of SUMIF in the same column in one of my earlier tutorial below. It’s a dedicated tutorial that only covering the said problem.
Must Read: Sumif When Multiple Criteria in the Same Column in Google Sheets
3. Google Sheets Formula to Sum A Column Based on Conditions in Two Columns – AND Logic
What we want to apply here is the AND Logic, See that both in Query and SUMIF.
Sum column B if the values in Column A is “Info Inspired” and Column C is 90.
=query(A1:D10,"Select Sum (B) where A='Info Inspired' and C=90")
I am recommending you to use Google Sheets SUMIFS when there are conditions in different columns.
=sumifs(B2:B10,A2:A10,"Info Inspired",C2:C10,90)
Note:
Why SUMIFS shows the error Array arguments to SUMIFS are of different size?
The criteria range and criterion should be separated by “,” not “=”. It’s a common mistake happen in the use of SUMIF or SUMIFS.
4. Sum A Column Based on Conditions in Two Columns – OR Logic
Sum Column B, if column A is “Info Inspired” and Column D is “High” or “Moderate”.
Here you can learn the use of AND, OR logical clause in Query. First, see the formula then I’ll tell you on which part of this formula you should take care of.
=query(A1:D10,"Select Sum (B) where A='Info Inspired' and (D='High' or D='Moderate')")
Here the multiple OR conditions should be enclosed in brackets in Query in Google Sheets. In the above formula, you can see that the multiple conditions in the same column are put inside the brackets.
How to use AND, OR in SUMIFS?
Here is the combination of SUM, SUMIFS, REGEXMATCH, and ARRAY.
Tutorial: REGEXMATCH in SUMIFS and Multiple Criteria Columns
From my experience, when you have multiple conditions in SUM, I recommend you to use Query as it’s easier to remember.
5. Google Sheets Formula to Sum A Column Based on Conditions in Two or More Columns – AND, OR Logic
The use of two AND conditions and two OR conditions in Google Sheets Query and as well as in SUMIFS.
This formula is almost the same as the formula in Point # 4 above. First, see the Query formula that using two AND clauses and two OR clauses.
=query(A1:D10,"Select Sum (B) where A='Info Inspired' and C=90 and (D='High' or D='Moderate')")
Here is the Complex SUMIFS formula to handles multiple AND, OR conditions in Google Sheets.
You may think a Sumifs formula like the one below would work. But it won’t.
Non-Working Sumifs:
=ArrayFormula(sum(sumifs(B2:B10,A2:A10,"Info Inspired",C2:C10,90,D2:D10,{"High";"Moderate"})))
Why doesn’t this formula work?
Because the Curly Bracket is not supported in SUMIFS. But it does support in SUMIF.
Can you give me the solution then? Yes! Use the SUMIFS + Regexmatch Combo. You can find the link to that tutorial under Point # 4 above.
Conclusion
I’ve provided you different Google Sheets formulas to sum a Column based on multiple conditions. I hope I could cover most of your Google Sheets conditional SUM requirements. That’s all. Enjoy!
