With the help of an array formula (helper column) in the source data, we can sum Min or Max Values in Pivot Table in Google Sheets.

In this post, I am trying to address one of the issues you may face when using the functions MIN or MAX within the pivot table editor.

What’s that?

When you enable “Show Totals” of COLUMNS or ROWS in the Pivot Table editor and use either of the functions, Min or Max, in the VALUES field, you may face one issue.

As a side note, the COLUMNS, ROWS, AND VALUES are the sections within the Pivot Table editor where you can add/drag-drop fields available within your source data.

The issue is, the Grand Total columns may return Min of Min or Max of Max.

What you might want would be the Sum of Min or Sum of Max in Grand Totals.

What we want: ✓

(Sum of Min Values in Pivot Table Grand Total Row and Column)

What we usually get: ✖

(Min of Min Values in Pivot Table Grand Total Row and Column):

Please take some time to understand the difference between the pivot tables in images 1 and 2.

Now let’s proceed to the solution.

How to Sum Min Values in Pivot Table in Google Sheets?

We are going to prepare two pivot tables (please see image # 1 above). So there will be two examples below.

In the first one (pivot table in A1:B4), we will group the data by Item, whereas, in the second one (pivot table in D1:G5), we will group the data by Date and Item.

Example 1 (Pivot Table 1)

I have used the following data to create the above pivot table reports.

In the following example, we will group the field Item.

Usually, the Pivot Table Editor settings for the same will be as follows.

1. Data Range: A2:C10

2. Rows: Item, Show Totals

3. Columns: None

4. Values: Receipt

Summarize by: Min

What we should follow is;

1. Data Range: A2:D10

2. Rows: Item – Show Totals

3. Columns:

4. Values: Min – Here, Min is the helper column range D2:D10 (image # 3).

Summarize by: Sum

That means we should use a helper column (D2:D10) to Sum Min values or solve the Grand Total issue in Pivot Table in Google Sheets.

Here are the formulas to generate that column.

How to Generate the Helper Min Column?

In cell E3, insert the following running count array formula.

=ArrayFormula(countifs(row(B3:B10),"<="&row(B3:B10),B3:B10,B3:B10))

It will return the running count of the Items in the range B3:B10.

In cell F2, use the below Query to group the field Item and return the minimum receipt of the item in each group.

=ArrayFormula(query({B2:B10&"~"&1,C2:C10},"Select Col1,min(Col2) group by Col1 label Col1'',min(Col2)''",1))

I have used {B2:B10&"~"&1,C2:C10} instead of B2:C10. It’s because I want to add the character ~1 with each item (B2:B10).

Please see the above screenshot (image # 3) to understand the output of the above two formulas.

You can learn the above Query grouping here – Filter Min or Max Value in Each Group in Google Sheets.

Why should we add ~1 to the grouped Item?

Adding ~1 is important in getting the sum of min or max values in the pivot table.

Here is why.

We want to assign the minimum value returned by the Query to the source data in D3:D10. Not in all the rows, but in the first row of each group.

For that, we will depend on the running count output. How?

We have already added ~1 to the grouped items in the Query.

Now we will add ~ sign and running count (E3:E10) to the items in the source (B3:B10) and use that as the search key in Vlookup.

Here is how.

D2 Formula:

={"Min";ArrayFormula(IFNA(vlookup(B3:B10&"~"&E3:E10,F2:G3,2,0)))}

The range in Vlookup, i.e. F2:G3, is the Query output.

The above formula will assign the minimum value in each group to the first row of each group.

If you want, you can combine the above three formulas and use that in cell D2.

={"Min";ArrayFormula(IFNA(vlookup(B3:B10&"~"&countifs(row(B3:B10),"<="&row(B3:B10),B3:B10,B3:B10),query({B2:B10&"~"&1,C2:C10},"Select Col1,min(Col2) group by Col1 label Col1'',min(Col2)''",1),2,0)))}

Note:-

Since we use a helper column in the pivot report, the field labels may not be the ones we want in the output.

We can modify that. For example, double-click cell B2 in the pivot report and rename the label to “Min of Receipt.”

Example 2 (Pivot Table 2)

In the following example, we will group the fields Date and Item.

You have already learned how to sum Min values in Pivot Table in Google Sheets with grouping one column.

Here we require to make minor changes in the formulas as well as in the pivot table.

What if we want to group by the fields Date and Item?

Please see the Pivot Table in D1:G5 (image # 1).

To get that report, here are the changes required.

Changes in the Formulas

Running Count in E3:

=ArrayFormula(countifs(row(B3:B10),"<="&row(B3:B10),A3:A10&B3:B10,A3:A10&B3:B10))

I have replaced the range reference B3:B10 with the range A3:A10&B3:B10.

Query in F2:

=ArrayFormula(query({A2:A10&B2:B10&"~"&1,C2:C10},"Select Col1,min(Col2) group by Col1 label Col1'',min(Col2)''",1))

Replaced B2:B10 with A2:A10&B2:B10.

Vlookup in D2:

={"Min";ArrayFormula(IFNA(vlookup(A3:A10&B3:B10&"~"&E3:E10,F2:G5,2,0)))}

I have replaced the range reference B3:B10 with A3:A10&B3:B10 and F2:G3 with F2:G5.

Combined Formula in E2:

={"Min";ArrayFormula(IFNA(vlookup(A3:A10&B3:B10&"~"&countifs(row(B3:B10),"<="&row(B3:B10),A3:A10&B3:B10,A3:A10&B3:B10),query({A2:A10&B2:B10&"~"&1,C2:C10},"Select Col1,min(Col2) group by Col1 label Col1'',min(Col2)''",1),2,0)))}

Changes within the Pivot Table Editor

Rows: Date – Show Totals

Columns: Item – Show Totals

Values: Min – Here, Min is the helper column range D2:D10.

Summarize by: Sum

This way, we can get the sum of Min values in the Pivot Table in Google Sheets.

How to Sum Max Values in Pivot Table in Google Sheets?

We can follow the above same steps to sum max values in Pivot Table in Google Sheets.

I am not repeating all the steps once again. Instead, let me direct you where to make the changes.

In the above examples, we have used the MIN function in Query. Replace that with Max.

Related:- How to Sum, Avg, Count, Max, and Min in Google Sheets Query.

In the Query, you can see min(Col2) twice. Replace both of them with max(Col2).

Then in Vlookup, replace the label “Min” with “Max.”

Those are the changes we require to sum max values in a Pivot Table report in Google Sheets.

This way, we can get the sum of min or max instead of the min of min or max in Pivot Table Grand Totals in Google Sheets.

Thanks for the stay. Enjoy!

Example Sheet 161021