SUMIF in Power Query

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.



Posted in Blog.

Leave a Reply

Your email address will not be published. Required fields are marked *