---
title: "Sum, Count, and Aggregate Related Instances in Workday"
description: "Learn why Workday Aggregate Related Instances returns object lists instead of totals, and how to pair ARI with Sum and Count Related Instances for rollups."
url: https://kiweely.com/guides/workday-aggregate-related-instances-ari-sum-count
---

# How to Sum or Count Multi-Instance Rows in Workday Using Aggregate Related Instances (ARI)

Workday Aggregate Related Instances does not add numbers or count rows: it collects unique related objects across a relationship into one deduplicated multi-instance list. Pair ARI with Sum Related Instances, Count Related Instances, and Arithmetic Calculation for numeric rollups.

-   **Checked** against Workday on November 13
-   Applies to **Workday Calculated Fields**
-   6 min read
-   Published November 13
-   By Kiweely

Workday’s **Aggregate Related Instances (ARI)** function does not add numbers, count records, or compute averages: it gathers related object instances across a relationship, strips out duplicates, and returns an alphabetical multi-instance object list. To calculate numeric totals, counts, distinct counts, or averages over multi-instance rows, use **Sum Related Instances** and **Count Related Instances** directly for one-hop relationships, or place **Aggregate Related Instances** underneath them when bridging a two-hop object path.

## ARI vs. Sum Related Instances vs. Count Related Instances

Report writers familiar with SQL `SUM()`, `COUNT()`, and `AVG()` routinely open **Create Calculated Field**, search for “Aggregate,” and pick **Aggregate Related Instances** expecting an aggregation dropdown. Instead, Workday prompts for **Source Field**, an optional **Condition**, and **Fields to Aggregate** (which accepts only single-instance or multi-instance object fields, never numbers or currencies).

Each multi-instance rollup requirement maps to a distinct function in Workday (all secured under the **Custom Field Management** domain in the **System** functional area):

| Calculated Field Function | Return Field Type | Deduplicates Automatically? | What It Actually Does |
| --- | --- | --- | --- |
|  **Aggregate Related Instances (ARI)** | `Multi-Instance` (object references) | Yes, excludes duplicate instances and sorts alphabetically | Collects all related object instances from `Fields to Aggregate` across every item in `Source Field` (for example, all unique `Job Profile` instances across all `Positions` in an `Organization`) |
|  **Sum Related Instances (SRI)** | `Numeric` or `Currency` | Sums every matching instance | Totals a numeric or currency `Field to Sum` across `All instances` or `Instances where condition is true` on a related business object |
|  **Count Related Instances (CRI)** | `Numeric` (integer) | Counts every instance in the target field (deduplicated only if fed by ARI) | Counts how many instances exist in `Related Field` across `All instances` or `Instances where condition is true` |
|  **Extract Multi-Instance (EMI)** | `Multi-Instance` (object references) | Depends on Subset, Intersection, Except, or Union operation | Filters a multi-instance field on a single hop by a Boolean condition or compares two multi-instance fields on the same business object |

If your report can use a Matrix layout instead of row-level calculated fields on an Advanced report, compare this approach with native [Workday Matrix report aggregations and drilldowns](https://kiweely.com/guides/workday-matrix-reports-aggregations-drilldowns), which summarize indexed data sources faster at scale.

## How to Sum or Count One-Hop Multi-Instance Rows Correctly

When the rows you want to total or count sit directly on a related business object of your Primary Business Object (PBO), such as `Expense Report Lines` on `Expense Report`, `Dependents` on `Worker`, or `Supplier Invoice Lines` on `Supplier Invoice`, you do not need **Aggregate Related Instances** at all.

First, inspect the relationship on the **Business Object Details** report to confirm the exact multi-instance field linking your PBO to the Related Business Object (RBO) and note the RBO’s name. If you only want to sum or count a subset of rows (for example, taxable invoice lines, meal expense items, or involuntary terminations), create the **True/False Condition** calculated field on the **Related Business Object**, not on the PBO:

Works

```
# Step 1: Filter condition on the Related Business Object
Task:            Create Calculated Field
Field Name:      CF_TF_Is_Meal_Expense_Line
Business Object: Expense Report Line          # Must be the RBO, NOT Expense Report
Function:        True/False Condition
Condition:       Expense Item in the selection list (Meals, Business Meals)

# Step 2: Conditional sum on the Primary Business Object
Field Name:      CF_SRI_Total_Meal_Lines_Amount
Business Object: Expense Report
Function:        Sum Related Instances
Related Field:   Expense Report Lines
Sum:             Instances where condition is true
Condition:       CF_TF_Is_Meal_Expense_Line
Field to Sum:    Converted Amount (or Amount in Corporate Currency)
```

Mixed currencies break Sum Related Instances

When **Field to Sum** is a `Currency` field, every summed instance must use the same currency code or Workday displays an error. On multi-currency objects like global expense lines or worker benefit costs, first build a **Convert Currency** calculated field on the RBO (backed by rates in **Maintain Currency Conversion Rates**), then select that converted field as your **Field to Sum**.

## How to Compute an Average Over Multi-Instance Rows

Workday does not ship an `Average Related Instances` calculated field function. Whenever you need an instance-level average on a PBO (such as average performance review score per organization, or average meal line amount per expense report for a business process approval rule), combine three fields:

1.  **Numerator (`Sum Related Instances`):** Sum the target numeric or currency field across the RBO. If rows with a blank or zero score should be excluded from the average, select **Instances where condition is true** and apply a **True/False Condition** on the RBO that excludes unrated or ineligible rows.
2.  **Denominator (`Count Related Instances`):** Count instances on the exact same **Related Field** using the **exact same Boolean condition**. Using `All instances` on the count while filtering the sum skews the average downward whenever excluded rows exist.
3.  **Quotient (`Arithmetic Calculation`):** Divide the **Sum Related Instances** field by the **Count Related Instances** field, and check **Return Zero on Error** on the Calculation tab. Without **Return Zero on Error**, any worker or organization with zero matching child instances triggers a divide-by-zero runtime error instead of returning `0`.

Keep in mind that numeric calculated fields display up to 6 decimal places in Workday, while currency calculated fields follow the ISO decimal precision of the currency code (such as 2 decimal places for `USD`).

## When You Actually Need Aggregate Related Instances: Two-Hop Rollups and Distinct Counts

**Aggregate Related Instances** becomes essential in two scenarios where **Sum Related Instances** and **Count Related Instances** cannot reach the target data on their own:

### 1\. Bridging a Two-Hop (1:M to 1:M) Relationship

Suppose your report PBO is `Organization` (or `Supervisory Organization`), which has a multi-instance `Positions` field, and each `Position` has a `Job Profile` (or multi-instance `Workers` who each have multi-instance `Dependents` or `Time Off Entries`). Because **Count Related Instances** and **Sum Related Instances** only traverse a single **Related Field** hop from the PBO, they cannot jump directly from `Organization` to `Job Profile` or grandchild lines.

Build an **Aggregate Related Instances** field on the PBO first to collect the grandchild instances into a single multi-instance field on the PBO, then point **Count Related Instances** or **Sum Related Instances** at that ARI field:

Works

```
# Hop 1 + 2 flattened onto Organization via Aggregate Related Instances
Field Name:          CF_ARI_Org_Job_Profiles
Business Object:     Organization
Function:            Aggregate Related Instances
Source Field:        Positions (or Active Filled Positions)
Condition:           (Optional Boolean on Position, e.g., Is Open Position = False)
Fields to Aggregate: Job Profile

# Distinct count of Job Profiles in that Organization
Field Name:          CF_CRI_Distinct_Job_Profiles_In_Org
Business Object:     Organization
Function:            Count Related Instances
Related Field:       CF_ARI_Org_Job_Profiles
Count:               All instances
```

### 2\. Counting Distinct Child Values Versus Total Child Rows

Because **Aggregate Related Instances** automatically deduplicates repeated object instances, wrapping **Count Related Instances** around an ARI field gives you an exact `COUNT(DISTINCT)` on an Advanced report. In the configuration above, if an organization has 40 filled positions sharing 6 job profiles, counting `Positions` directly returns `40`, whereas counting `CF_ARI_Org_Job_Profiles` returns `6`.

By the same rule, **be careful when feeding an ARI field into `Sum Related Instances`**. If two child records reference the exact same shared object instance, ARI collapses that duplicate instance to one entry before **Sum Related Instances** adds it up. Only feed ARI into **Sum Related Instances** when each grandchild transaction is a distinct business object instance (such as unique `Expense Report` or `Payroll Result` events across workers), never when multiple rows point to a shared lookup record.

## Verifying Security and Row Totals Before Deploying

Both **Sum Related Instances** and **Count Related Instances** enforce Workday’s instance-level domain security silently: Workday sums or counts **only the related instances the running user has permission to view**. If an HR Partner or Integration System User lacks domain access to compensation, absence, or certain organizations, the field does not error out; it simply returns a smaller total or `0` (see [why calculated fields return blank or unexpected values](https://kiweely.com/guides/calculated-field-returns-blank)).

To verify your rollup end to end:

1.  Run **View Security for Securable Item** on the underlying Workday-delivered fields to confirm every target persona or Integration System User has access to the domains that secure them.
2.  Build a quick audit Advanced report on the PBO showing the raw multi-instance **Related Field** alongside your ARI, **Count Related Instances**, and **Sum Related Instances** columns for 5 to 10 known records (including one record with zero child rows and one with duplicate child values).
3.  Test the report as the actual end-user persona or integration account rather than relying solely on an unconstrained administrator view.

When a rollup spans unfamiliar business objects and you do not want to hunt through **Business Object Details** by trial and error, [Kiweely Vine](https://kiweely.com/vine) inspects your live tenant metadata, maps the exact one-to-many object path between your report’s PBO and target fields, and builds the ARI, SRI, and CRI chain with your approval. See [how Kiweely Vine works](https://kiweely.com/vine/how-it-works) against live Workday tenants.

## Frequently asked questions

### Why does Aggregate Related Instances not let me pick a numeric or currency field to sum?

In Workday, Aggregate Related Instances is an instance-retrieval function, not a math function. It collects single-instance or multi-instance object references across a related business object, removes duplicates, and outputs an alphabetical multi-instance list. To add numbers or currencies, select Sum Related Instances instead.

### How do I calculate an average across multi-instance rows on a Worker or Organization?

Workday has no standalone Average Related Instances calculated field function. Build one Sum Related Instances field and one Count Related Instances field using the exact same Boolean condition on the related business object, then divide Sum by Count in an Arithmetic Calculation field with Return Zero on Error enabled.

### Why do two users running the same report see different Sum Related Instances or Count Related Instances totals?

Both Sum Related Instances and Count Related Instances evaluate security per row and include only the related instances the running user has permission to view. Check the domains securing the underlying fields with View Security for Securable Item.

### How do I count distinct values across a one-to-many relationship in an Advanced report?

Create an Aggregate Related Instances calculated field pointing to the target object field, which automatically strips duplicate instances from the returned list. Then build a Count Related Instances calculated field referencing that Aggregate Related Instances field and selecting All instances.

## Sources checked

-   [Aggregate Related Instances](https://doc.workday.com/admin-guide/en-us/manage-workday/tenant-configuration/calculated-fields/calculated-field-functions/dan1370797397899.html)
-   [Sum Related Instances](https://doc.workday.com/admin-guide/en-us/manage-workday/tenant-configuration/calculated-fields/calculated-field-functions/dan1370796335424.html)
-   [Count Related Instances](https://doc.workday.com/admin-guide/en-us/manage-workday/tenant-configuration/calculated-fields/calculated-field-functions/dan1370796334324.html)
-   [Reference: Calculated Field Functions](https://doc.workday.com/admin-guide/en-us/manage-workday/tenant-configuration/calculated-fields/calculated-field-functions/msq1571178141504.html)

## Related guides

[All guides](https://kiweely.com/guides)

-   [### How to Build Multi-Condition Evaluate Expression Calculated Fields in Workday Without Hitting Nesting Limits Workday has no fixed level cap on nested calculated fields, so deep chains fail at the 30-minute report runtime ceiling instead of saving with an error. Flatten multi-branch logic into one ordered Evaluate Expression grid, or replace static branches with Evaluate Expression Band.](https://kiweely.com/guides/workday-evaluate-expression-nested-calculated-fields)
-   [### How do I group, aggregate and drill down in Workday advanced, matrix and composite reports? Advanced reports group with Sort and Group and aggregate per column, matrix reports use row and column groupings, and composite reports combine subreports and drill through links on the composite itself. Worked examples below.](https://kiweely.com/guides/workday-matrix-reports-aggregations-drilldowns)
-   [### Workday Calculated Fields: How to Build Them and Fix Common Errors Choose the business object and scope, configure three core functions, and fix missing fields, empty results and incorrect conditions. Examples use Workday’s documented fields and options.](https://kiweely.com/guides/workday-calculated-fields)
-   [### Workday calculated fields tutorial: every function type with a worked example, including extract single instance Create a calculated field with the Create Calculated Field task: pick the business object, then a function. This tutorial gives one worked example for each function family, including extract single instance.](https://kiweely.com/guides/workday-calculated-field-types-examples)
-   [### Why does my Workday calculated field return blank in a report? A calculated field returns blank when its function finds nothing to return, when its business object is not reachable from the report, or when the viewer cannot see a field it reads. Test one known instance first.](https://kiweely.com/guides/calculated-field-returns-blank)

### More guides on Reports and calculated fields

[All 25 guides](https://kiweely.com/guides#reporting)

-   [Blank vs. Empty in Workday Report Filters and Condition Rules: Why Your Filter Misses Rows](https://kiweely.com/guides/workday-blank-vs-empty-report-filter-prompts)
-   [How to Fix CellLimitExceededException: Exceeded Cell Limit in Workday Custom Reports and EIBs](https://kiweely.com/guides/workday-report-cell-limit-exceeded-exception)
-   [How to Build a Workday Payroll Summary Report by Worker and Earning Code Over Any Date Range](https://kiweely.com/guides/workday-payroll-aggregate-earnings-deductions-report)
-   [How to Build an Extract Single Instance (ESI) Calculated Field in Workday (With Examples)](https://kiweely.com/guides/workday-extract-single-instance-calculated-field)
-   [Workday Date Calculated Fields: Increment or Decrement Date and Date Difference](https://kiweely.com/guides/workday-calculated-field-date-difference-increment)
-   [Why does a shared Workday report show blank columns or fewer rows to another user?](https://kiweely.com/guides/workday-custom-report-sharing-security-empty)
-   [Slow Workday Custom Reports: Fixing Data Source Filter and Calculated Field Timeouts](https://kiweely.com/guides/workday-slow-custom-report-performance-tuning)
-   [Workday composite reports: fix Map Fields For Join errors, prompt sets and subreport problems](https://kiweely.com/guides/workday-composite-report-errors)

### From the blog on Reports and calculated fields

[All posts](https://kiweely.com/blog)

-   [How to Review an AI-Built Calculated Field Chain Like a Senior HCM Lead](https://kiweely.com/blog/review-ai-built-calculated-field-like-senior-hcm)
-   [Approving Workday Expense Reports and Purchase Requisitions in Teams and Slack](https://kiweely.com/blog/workday-expense-report-and-requisition-approvals-in-teams-slack)
-   [Untangling a 14-Subreport Workday Composite Report Nobody on Your Team Built](https://kiweely.com/blog/reverse-engineering-inherited-workday-composite-reports)

## Let Workday check the work.

Kiweely writes WQL and builds orchestrations in your own tenant, and Workday, not Kiweely, says whether they passed.

[Start for free](https://kiweely.com/download) [See how it works](https://kiweely.com/vine/how-it-works)

## Get the next guide in your inbox.

One email a month with new guides and one Extend tip. Nothing else.
