Power Query Classic COUNTIFS | SUMIFS | AVERAGEIFS | MAXIFS | MINIFS Calculations [Simply Amazing]

Welcome to Excel Jet Consult blog post. In this Excel tutorial, we will how to create COUNTIFS, SUMIFS, AVERAGEIFS, MAXIFS and MINIFS calculation using Power Query and Worksheet Functions.

Video

Sample Data

In the caption below, we have the Sales Data that is officially formatted as an Excel Table. fSalesData is given as the Table Name

Load to Power Query

Next, we want to load the data set into the Power Query. To do that,

• Click inside the data.
• In the Get & Transform Data group of the Data tab, click on From Table/Range

From the caption below, we can see that the dataset in now in the Power Query editor

Duplicate fSalesData Table

Next, I want to Duplicate the fSalesData table. To do that,

• Right-click on the fSalesData Query to the left of the screen
• Select Duplicate
• Then, renamed the duplicate as ResultTable

Remove Unwanted Columns

Next, we want to remove unwanted columns such as Date, Units, Price and Sales Amount. To do that,

• Right-click on the above listed columns and select Remove. 

We have the unique Payment and Brand each having eight rows as we can see in the caption below

Reference fSalesData Table

Next, we want to reference the fSalesData table to be in each of the rows. To do that,

• In the Add Column tab, click on Custom Column
• Provide COUNTIFS as the column name
• In the Custom Column formula, execute the following M code: fSalesData

• Click OK. As we can see in the caption below, we have the Table in each of the cells

COUNTIFS Calculation in Power Query

We want to handle the COUNTIFS operation using the Power Query. To do that,

• In the Applied Steps located right of the screen, click on the edit icon located beside the Add Column step
• In the Custom Column formula, execute the following M code:

List.Count(
Table.SelectRows(
fSalesData,
(MyVariable) => MyVariable[Payment]=[uPayment]
and
MyVariable[Brand]=[uBrand]
)
[Units]
)

• Click OK.

From the caption below, we have the COUNTIFS solution

SUMIFS Calculation in Power Query

For the SUMIFS Calculation, follow the steps below

• In the Add Column tab, click on Custom Column
• Provide SUMIFS as the column name
• In the Custom Column formula, execute the following M code:

List.Sum(
Table.SelectRows(
fSalesData,
(MyVariable) => MyVariable[Payment]=[uPayment]
and
MyVariable[Brand]=[uBrand]
)
[Sales Amount]
)

• Click OK

AVERAGEIFS Calculation in Power Query

For the AVERAGEIFS Calculation, follow the steps below

• In the Add Column tab, click on Custom Column
• Provide AVERAGEIFS as the column name
• In the Custom Column formula, execute the following M code:

List.Average(
Table.SelectRows(
fSalesData,
(MyVariable) => MyVariable[Payment]=[uPayment]
and
MyVariable[Brand]=[uBrand]
)
[Sales Amount]
)

• Click OK

MAXIFS Calculation in Power Query

For the MAXIFS Calculation, follow the steps below

• In the Add Column tab, click on Custom Column
• Provide MAXIFS as the column name
• In the Custom Column formula, execute the following M code:

List.Max(
Table.SelectRows(
fSalesData,
(MyVariable) => MyVariable[Payment]=[uPayment]
and
MyVariable[Brand]=[uBrand]
)
[Sales Amount]
)

Click OK

MINIFS Calculation in Power Query

For the MINIFS Calculation, follow the steps below

• In the Add Column tab, click on Custom Column
• Provide MINIFS as the column name
• In the Custom Column formula, execute the following M code:

List.Min(
Table.SelectRows(
fSalesData,
(MyVariable) => MyVariable[Payment]=[uPayment]
and
MyVariable[Brand]=[uBrand]
)
[Sales Amount]
)

Click OK

From the screenshot below, we have the solution for each calculations in the Power Query

Note, we have applied Currency and Whole Number Data Types to the result columns

Import to Excel Sheet

Next, we want to load to Excel sheet. However, we want want to dump the two tables (fSalesData and ResultTable queries) into the Excel sheet. Therefore, we are going to first and foremost, create connection only. To do that,

• In the Home tab, select Close & Load To
• In the Import Data wizard, check the Only Create Connection as seen below

• In the Querries and Connection task pane, right-click on the ResultTable query and Select Load To
• Next, select Table and select Existing Worksheet and the destination cell. In this case, we dumped the table in cell H1

• Click OK

From the caption below, we have the Power Query classic solution in Excel Worksheet

COUNTIFS, SUMIFS, AVERAGE, MAXIFS, MINIFS using Worksheet Functions

Finally, we want to perform the COUNTIFS, SUMIFS, AVERAGE, MAXIFS and the MINIFS using the worksheet functions. To do that,

• In cell J13, execute the formula: =COUNTIFS(fSalesData[Payment],H13:H20,fSalesData[Brand],I13:I20)
• Click Enter
• In cell K13, execute the formula: =SUMIFS(fSalesData[Sales Amount],fSalesData[Payment],H13:H20,fSalesData[Brand],I13:I20)
• Click Enter
• In cell L13, execute the formula: =AVERAGEIFS(fSalesData[Sales Amount],fSalesData[Payment],H13:H20,fSalesData[Brand],I13:I20)
• Click Enter
• In cell M13, execute the formula: =MAXIFS(fSalesData[Sales Amount],fSalesData[Payment],H13:H20,fSalesData[Brand],I13:I20)
• Click Enter
• In cell N13, execute the formula: =MINIFS(fSalesData[Sales Amount],fSalesData[Payment],H13:H20,fSalesData[Brand],I13:I20)
• Click Enter

From the caption below, we can clearly see that we have EXACT result with the Power Query solution and the Worksheet functions

See you in the next tutorial