SUMIF: Unlocking the Power of Multiple Columns in Google Sheets

Are you tired of using complex functions to summarize your data in Google Sheets? Well, get ready to be blown away! In this article, I’ll show you how to use the SUMIF function to return multiple columns in Google Sheets, giving you a whole new level of flexibility and control.

Introducing SUMIF Two-Dimensional Multiple Column Outputs

Imagine being able to generate a category-wise summary with just a few clicks. With SUMIF, you can do exactly that. Let’s dive into the details.

How to Use SUMIF to Return Multiple Columns

To harness the power of SUMIF, you need to understand how to include search keys in the function. Here’s a step-by-step guide:

SUMIF to Generate Date and Category Wise Summary

To create a date and category-wise summary, follow these instructions:

1. Place your sample data in the ‘Sales’ tab.
2. Enter your criteria and formula in the ‘Summary’ tab.
3. Use the following formula in cell B2: =ArrayFormula(if(A2:A<>"",sumif(Sales!$B$2:$B&Sales!$A$2:$A,$B$1:$G$1&$A$2:$A,Sales!$C$2:$C),))

You can see that the SUMIF formula returns a two-dimensional array result, providing you with a comprehensive summary.

SUMIF to Generate Month and Category Wise Summary

If you want to create a month and category-wise summary, you can modify the previous formula by wrapping the range in the MONTH function. Here’s how:

1. Use the following formula in cell B2: =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),))

Generate a Year and Category Wise Summary Using SUMIF

Creating a year and category-wise summary requires a simple modification to the formula. Follow these steps:

1. Use the following formula in cell B2: =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),))

How to Generate a Quarter and Category Wise Summary Using SUMIF

Creating a quarter and category-wise summary is a breeze with SUMIF. Although you can’t use the QUARTER function directly, you can leverage a custom formula called “Extract Quarter from a Date in Google Sheets.” Here’s how:

1. Use the following formula in cell B2: =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),))

By following these formulas, you can easily generate insightful reports with SUMIF.

SUMIF Returns Multiple Columns Output Across the Years Range

But what if you need to analyze data across multiple years? Don’t worry; SUMIF has got you covered. Here’s how you can achieve a month and category-wise summary across the years:

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

To generate a summary across the years, modify the Formula #2 as follows:

1. Replace the month(Sales!$A$2:$A) part in the formula with month(Sales!$A$2:$A)&"_"&YEAR(Sales!$A$2:$A).
2. Use the following formula in cell B2: =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),))

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

To generate a quarter and category-wise summary across the years, modify the Formula #4 as follows:

1. Change the SUMIF range Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0) to Sales!$B$2:$B&ROUNDUP(month(Sales!C2:C)/3,0)&"_"&YEAR(Sales!$A$2:$A).
2. Use the following formula in cell B2: =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),))

That’s it! With these formulas, you can unlock the true potential of SUMIF and unleash powerful insights from your data.

So, what are you waiting for? Head over to Crawlan.com to discover more tips and tricks for making the most out of Google Sheets. Get ready to impress your friends and colleagues with your newfound expertise!

Happy analyzing!