SUMIF Returns Multiple Columns in Google Sheets

A two-dimensional array result is possible with the SUMIF function in Google Sheets. This will be useful in category wise summary. Let’s see how SUMIF returns multiple columns in Google Sheets.

In this tutorial, to demonstrate a two-dimensional array result, I am going to use the SUMIF to create a category and date/month/quarter/year wise summary.

I know there won’t be many takers for this SUMIF topic via search as many SUMIF users even won’t think about such a dynamic solution using SUMIF. So I am writing this post keeping my frequent visitors in my mind.

Are you new to SUMIF in Sheets? Then here is my Sheets functions guide.

SUMIF Two-Dimensional Multiple Column Outputs in Sheets

Here are the sample data and the date and category wise summary using SUMIF (two-dimensional SUMIF array result).

SUMIF Returns Multiple Columns - Two-Dimensional SUMIF

You can follow this same approach for SUMIF based month/quarter/year wise summary. Find the formula and how to code the formula (formula explanation) below.

Note:

My sample data for all the below examples are in the ‘Sales’ tab. The criteria and the formula are in the ‘Summary’ tab.

How to Use SUMIF to Return a Multiple Column Output in Google Sheets

Take a look at the SUMIF two-dimensional output in the above date and category wise summary. In that, column A and Row 1 contains the search keys.

How you are going to include these search keys in SUMIF is the answer to how SUMIF returns multiple columns in Sheets.

I am going to explain this SUMIF horizontal and vertical criteria use below under different subtitles.

SUMIF to Generate Date and Category Wise Summary in Sheets

My sample data is in the ‘Sales’ tab A1:C. The formula is in the ‘Summary’ tab B2. Before coming to that formula, refer the SUMIF function syntax below. That will help me to explain the formula better.

SUMIF(range, criterion, sum_range)

My SUMIF formula in cell B2 is returning a two-dimensional array result. With the SUMIF, we must use the ArrayFormula function. So that the SUMIF will expand.

The SUMIF syntax for the multiple column output will be as follows.

ArrayFormula(SUMIF(range, criteria, sum_range))

Formula # 1: SUMIF Date and Category Wise Summary.

=ArrayFormula(if(A2:A<>"",sumif(Sales!$B$2:$B&Sales!$A$2:$A,$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))

Formula Explanation:

Let me start the formula explanation with a screen capture.

SUMIF Generating a Date and Category Wise Summary in Sheets

range:

Check my sample data. In that, you can see that the first column contains the date and the second column contains the category.

I have combined these two columns (range) in the SUMIF formula as category&date, i.e. Sales!$B$2:$B&Sales!$A$2:$A.

criteria:

Since we have combined two columns in the range, the criteria must also be combined. Here I have arranged the criteria for SUMIF in a row (horizontally) and column (vertically).

Here also combine the criteria (category and date) as below.

$B$1:$G$1&$A$2:$A

Curious to know about these combinations? Enter the above SUMIF range and criteria in your ‘Summary’ tab as below. Do wrap the formulas with the ArrayFormula function and make the range finite (finite example: A2:A4 instead of A2:A, please refer to the screenshot). If you use infinite range, your Sheet might become unresponsive.

SUMIF Multi-Column Criteria in Google Sheets

The SUMIF returns multiple columns since we have the criteria in a two-dimensional array.

That reveals one important thing! To get a 2D array result using SUMF, feed it a 2D array criteria.

sum_rage:

It’s no doubt the column C in the sales tab.

This way you can use SUMIF to return a two-dimensional output, I mean a date wise and category wise summary in Google Sheets.

SUMIF to Generate Month and Category Wise Summary in Sheets

Using the above SUMIF formula you are one step away from creating a month and category wise summary report.

Just wrap the range in the above SUMIF multiple column formula with the MONTH function.

Must Read: How to Utilise Google Sheets Date Functions [Complete Guide].

Formula # 2: SUMIF Month and Category Wise Summary.

=ArrayFormula(if(A2:A<>"",sumif(Sales!$B$2:$B&month(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))

I may need to modify the sample data and the criteria to test the new formula. Here is that.

SUMIF Generating Month and Category Wise Summary in Sheets

This time I have used month numbers in the ‘Summary’ tab column A. That’s why I have used the MONTH function to convert the date to month only in the ‘range’ part of the formula. The formula in cell B2 returns an array output.

I have already shared two examples to make you understand how SUMIF returns multiple columns in Google Sheets.

Generate a Year and Category Wise Summary Using SUMIF in Sheets

To understand this part please learn the above monthly summary first. Because here I am only sharing what changes you must make to the formula above.

Formula # 3: SUMIF Year and Category Wise Summary.

=ArrayFormula(if(A2:A<>"",sumif(Sales!$B$2:$B&YEAR(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))

Changes in the Formula:

Change the MONTH function to YEAR.

Changes in the Criteria in the ‘Summary’ tab:

Enter the years in column A2:A instead of month numbers.

How to Generate a Quarter and Category Wise Summary Using SUMIF in Sheets

A quarter and category wise report is also possible with SUMIF. Here you haven’t the comfort of using the function QUARTER instead of MONTH/YEAR as it will only work in Query.

Then how to feed the SUMIF range with the quarters? I have a custom formula for that – Extract Quarter from a Date in Google Sheets. I am going to use that in the SUMIF two-dimensional output formula here.

Formula # 4: SUMIF Quarter and Category Wise Summary.

=ArrayFormula(if(A2:A<>"",sumif(Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))

Just pay your attention to the SUMIF ‘range’ in this formula. Needless to say, enter quarters 1, 2, 3, and 4 in the ‘Summary’ tab A2:A as the criteria.

Except for the first and third formula, the dates in column A in the ‘Sales’ tab must fall within a year range.

I mean the formula 2 and 4 (month and quarter) won’t return a correct result if the dates are in two different years or you can say across the years.

To generate the month/quarter and category wise summary across the years use the formulas as below.

SUMIF Returns Multiple Columns Output Across the Years Range in Sheets

For month and category wise summary across the years, change the MONTH formula in the Formula # 2 as below.

Here SUMIF returns multiple column output (two-dimensional output) in an across the year date range.

SUMIF Returns Month and Category Wise Multiple Colum Summary Output – Across the Years

See formula # 2. In that replace the month(Sales!$A$2:$A) part with month(Sales!$A$2:$A)&"_"&YEAR(Sales!$A$2:$A). So the final formula will be;

Formula # 5: SUMIF Month and Category Wise Summary Across the Years.

=ArrayFormula(if(A2:A<>"",sumif(Sales!$B$2:$B&month(Sales!$A$2:$A)&"_"&YEAR(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))

In the ‘Summary’ tab column A, enter the criteria as below. This way you can use month and year criteria in SUMIF in Sheets.

SUMIF Returns Multiple Columns - Month and Year Summary

SUMIF Returns Quarter and Category Wise Multiple Colum Summary Output – Across the Years

See formula # 4 above. Change the SUMIF range Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0) with Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0)&"_"&YEAR(Sales!$A$2:$A).

Formula # 6: SUMIF Quarter and Category Wise Summary Across the Years.

=ArrayFormula(if(A2:A<>"",sumif(Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0)&"_"&YEAR(Sales!$A$2:$A),$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))

Enter the criteria in the ‘Summary’ tab column A as 1_2018 (quarter_year).

This SUMIF will also return multiple columns in Google Sheets.

That’s all. Enjoy!