Welcome to Excel Jet Consult blog post. In this tutorial, we will learn how to perform SUMIF in Power Query. First and foremost, we will calculate Total for each Region using the traditional SUMIF function in Excel. Thereafter, we will do the same using Power Query approach. Let’s get started.
SUMIF Function adds all numeric values in a range of cells. However, SUMIFS is not available in Power Query.
SUMIF Syntax =SUMIF(range, criteria, [sum_range])
The capture below shows the data we will use for this tutorial
The caption below shows the Total by Region using SUMIF function
In the SUMIF formula as seen in the caption above, the first argument input value is the named range for Region. The second argument input value is the relative cell reference for each unique Region. The third input value represent the named range for Total
Next, we want to replicate the same calculation in Power Query of Excel. In the capture below, we have the same data loaded to Power Query
To calculate the Total for each Region:
In the Table group of the Transform tab, click on Group By
In the Group By dialogue box, we will Group by Region and provide Region Total as New column name
In the Operation and Column drop-downs, select SUM and Total respectively
Click OK
When we compare the result of the Power Query and Excel SUMIF, we will discover that we have the same Total.