The SUMIF function can work in Pivot Table calculated fields in Google Sheets, but not always. Let’s explore when it will work and, if it doesn’t, understand the reasons behind it and explore alternative solutions.

When you use a SUMIF formula in a calculated field within the Pivot Table, you will most likely encounter the “Argument must be a range” error.

The root cause of this error is that we need to use field labels in custom formulas in the Pivot Table instead of range references, which SUMIF does not support in the sum_range.

Furthermore, sum_range is an optional argument in SUMIF. Therefore, if you use a SUMIF formula without this argument in a calculated field, it will work; otherwise, it will not.

Introduction to Using SUMIF in Pivot Table Calculated Fields

A Pivot Table is an invaluable tool for filtering, summarizing, and analyzing data in Google Sheets. If you are not well-versed in using the QUERY function, then the easiest way to summarize complex data is by utilizing a Pivot Table.

The Pivot Table interface is user-friendly, allowing even novice users to effortlessly drag and drop field labels into Columns, Rows, and Values fields to create their reports.

It enables the performance of calculations, such as sums, averages, counts, or other aggregations (13 supported functions in total), on one or more columns.

Unlike formula-based results, a Pivot Table provides drill-down capability, enabling users to expand or collapse data in the report. Additionally, it allows for the creation of pivot date groups (which we will utilize below) in a date column.

Calculated fields are Value fields that enable users to add columns with custom formula-applied results.

For example, let’s say you want to release payments for suppliers whose outstanding liabilities are less than $10,000.

If your table contains fields like Date, Supplier ID, and Amount, you can summarize the table by grouping the Date column by month and applying a condition to the Amount column in the calculated field.

Typically, the SUMIF function is used for such types of conditional sums. Let’s see how to use SUMIF in Pivot Table calculated fields to solve this problem and also alternatives.

You should prioritize alternative solutions since SUMIF won’t work when you need to specify the sum_range argument in the formula. In this example, we do not want to specify this argument.

How to Use SUMIF in Pivot Table Calculated Fields in Google Sheets

Let’s try to use SUMIF in a Pivot Table calculated field and observe whether it works or not. After that, we will explore potential alternatives.

In the following example, you can observe dates in column A, supplier IDs in column B, and their outstanding liabilities in column C.

The Pivot Table displays the total outstanding liabilities by month. However, we are interested in the month-wise outstanding liabilities of suppliers with pending amounts of less than $10,000.

You can follow these steps to create the Pivot Table:

1. Select the data in A1:C11.
2. Click on Insert > Pivot Table.
3. Choose Existing Sheet and click on cell E1.
4. Click OK, then Create to generate the layout.
5. Drag and drop the Date field under Rows.
6. Drag and drop the Outstanding Amt filed under Values and select Sum under Summarize by.
7. Right-click on any value in column E in the Pivot Table and click on Create Pivot Date Group > Month.

The Pivot Table displays the total outstanding amount by month.

If you want to calculate the month-wise total of outstanding liabilities that are less than $10,000, you will need to use a calculated field.

You can employ the following SUMIF formula in the calculated field:

=SUMIF('Outstanding Amt',"<="&10000)

Syntax of the SUMIF Function:

SUMIF(range, criterion, [sum_range])

In the above formula, there’s no need to use the sum_range as the range and sum_range are the same. So, it will work flawlessly in the Pivot Table.

To add this formula to the Pivot Table calculated field, follow these steps:

1. First, remove the existing “Outstanding Amt” field under Values by clicking on the “x” button.
2. Click Add next to Values and select Calculated Field.
3. In the provided field, replace any existing formula (possibly an =0), then copy and paste the above SUMIF formula.
4. Select Custom under Summarize by. This is an important step.

This will add a calculated field to the Pivot Table. You can double-click cell F1 and replace “Calculated Field 1” with any custom text.

Let’s explore alternative solutions to SUMIF in the calculated field in the Pivot Table.

Exploring Alternative Solutions to SUMIF

In the above example, you can replace SUMIF with either of the following formulas:

=SUM(IFNA(FILTER('Outstanding Amt','Outstanding Amt'<10000)))
=SUMPRODUCT('Outstanding Amt'<10000,'Outstanding Amt')

I would recommend using SUMPRODUCT instead of SUM + FILTER because the former appears cleaner to me.

An Example of When SUMIF Fails in Pivot Table Calculated Fields

SUMIF fails when you use a text criterion because, in that case, you will be required to use all three arguments in the SUMIF function, including the sum_range.

In the Pivot Table, specifying the sum_range may result in an #N/A error, specifically the “Argument must be a range” error.

Let’s explore an example of this issue and discuss alternative solutions.

The field labels include Product, Status, and Qty. The Status column contains “x” marks, and the goal is to sum the Qty if the Status is “x.”

This is the SUMIF formula that you might attempt but it would return errors:

=SUMIF('Status',"x",'Qty')

Here are the working alternative solutions using SUMPRODUCT and SUM + FILTER:

=SUMPRODUCT(Status="x",Qty)
=SUM(IFNA(FILTER(Qty,Status="x")))

Note: In this case, you don’t actually need to rely on the calculated field. You can use Filters within the Pivot Table to filter out all rows that are not ‘x’ (blanks).

However, the advantage of using a calculated field here is that it will display all categories, regardless of whether their total is 0 or not.

Conclusion and Key Takeaways

The above are just examples of how to use SUMIF and alternatives in calculated fields in Pivot Tables in Google Sheets.

You should replace the formulas according to your specific data. To do that, you might want to learn how to use the SUMIF, FILTER, and SUMPRODUCT functions if you haven’t already.

You can refer to my Google Sheets Function Guide to learn more about these functions.

If you need specific solutions, feel free to ask in the comments below. Please include a sample Sheet URL in your comments.