Skip down to the research record

Sales calculation tools: 4 options for deal arithmetic

Quota Forecast Editorial Team · Published · 10 min read

Four sales calculation options cover linked rollups, row lookups, field formulas, and deal-only formulas.

Key takeaways

Select the calculation type based on whether your forecast input requires an aggregate sum, a single row match, or a formula applied to deal fields.

  • Airtable: Use a rollup field for aggregate calculations over linked records. If you need to see each linked record's value individually rather than an aggregate, add a lookup field instead, according to Airtable.
  • Google Sheets: VLOOKUP searches only in the search key column (when index = 1) or columns that are further right, according to Google.
  • Pipedrive: Formula fields calculate values only in deals, even when created using lead or deal custom fields, according to Pipedrive.
  • Pipedrive: Every formula must contain a “#” followed by an input field, such as #Deal Value, according to Pipedrive.

Choose the tool that matches your data structure: linked records for aggregates, column-based tables for lookups, or deal-specific fields for formulas.

Sales calculation tools: the documented options

A rollup field performs aggregate calculations that leverage formula functions on specific cells from records that are linked together, according to Airtable. This behavior distinguishes linked-record totals from single-row operations. The comparison below documents calculation types and restrictions for four tools. Use the table to identify which documented behavior matches your forecast input needs.

PublisherCalculation TypeScopeKey Restriction
AirtableLinked record aggregate calculationRecords linked togetherLeverages formula functions on specific cells
Google SheetsRow lookupsSearch column and columns farther rightNumbers lookup columns from one
FreshsalesField-based formulaChosen field as variableMIN example returns lowest Discount value
PipedriveDeal-field formulaDeals onlyRequires monetary or numerical field

Select the calculation type that matches the input structure in your pipeline.

How the four options were compared using their help pages

The comparison relies on official support documentation to verify how each tool handles calculated fields. This documentation distinguishes between aggregate results and individual values; if you want to see each linked record's value on its own instead of an aggregate, the page advises adding a lookup field instead, according to Airtable.

The methodology focuses on the distinct input restrictions and calculation scopes documented in the respective help centers. The analysis isolates the documented behavior for deal arithmetic without inferring capabilities that are not explicitly stated. By limiting the review to these specific help pages, the comparison avoids treating one publisher's rule as a universal standard for all sales tools.

Each tool’s documentation is examined for its specific handling of formula variables, lookup positions, and record scopes. The documentation for the other tools describes row lookups or field-based formulas, which differ from the aggregate calculations described in the Airtable source. This distinction is critical for RevOps owners who need to map calculated inputs to their weekly forecast accurately.

When selecting a calculated input for your forecast, check whether the tool’s documentation supports aggregate rollups over linked records or individual row lookups. Verify the specific array position rules or field variable constraints before mapping the data to your pipeline.

Linked record totals and individual values in Airtable

Airtable’s documentation distinguishes between aggregate calculations that sum linked data and individual-value selections that pull specific items from a linked record. For RevOps owners defining how calculated deal inputs enter the weekly forecast, this distinction determines whether a field returns a total or a discrete value. The "Rollup field overview | Airtable Help Center" page documents how to select specific items from a linked array using the ARRAYSLICE function. To get the first item from a linked value array, the documented formula is ARRAYSLICE(values, 1, 1), according to Airtable. This syntax retrieves a single value from the linked records, useful when a forecast input requires a specific attribute rather than an aggregate sum.

The same documentation provides the formula for retrieving the first two items: ARRAYSLICE(values, 1, 2), according to Airtable. This demonstrates that the selection mechanism is positional, allowing the user to define exactly which linked values appear in the field. The array selection starts at position one, meaning the first linked record is index 1, not 0. This positional numbering is critical for ensuring that the correct linked value is pulled into the forecast calculation. If a deal record links to multiple related records, the ARRAYSLICE function allows the RevOps owner to specify which of those linked values should be used in the formula, rather than relying on an aggregate function that might sum all of them.

The documentation also notes that when using VLOOKUP in related contexts, columns are numbered from left to right starting with 1, according to Google. For Airtable’s ARRAYSLICE function, this aligns with the documented behavior where the first item is at position 1.

Google Sheets lookup positions and Freshsales field variables

When a forecast input depends on matching a row in a spreadsheet, the position of the returned data matters. In VLOOKUP, the index parameter must be a number of 1 or greater; entering a number smaller than 1 for the index results in an invalid lookup index input, as documented in Google. The function’s behavior is further defined by the match type. For an exact match, the documentation recommends using FALSE, according to Google.

In contrast, Freshsales handles calculation inputs through field-based formulas rather than spreadsheet row lookups. The value from a chosen field is used as a variable in the formula, according to Freshsales. This approach allows specific data points to be incorporated directly into the arithmetic of a deal record. The documentation provides a concrete example of how a variable interacts with a constant: MIN(Discount, 20) looks into the “Discount” field in your web application and returns the lowest value (across all deals), while also considering the number 20, according to Freshsales.

For a related amount check, see Weighted deal amount: 5 fields to check HubSpot, Pipedrive.

The difference between these two methods is structural. Google Sheets VLOOKUP operates on the position of columns relative to a search column, where the index dictates which column is returned. Freshsales formulas operate on the values of specific fields, where the chosen field acts as a variable within a defined mathematical expression. For RevOps owners, this means that if a calculation requires pulling data from a different row based on a key, a spreadsheet lookup with a valid index of 1 or greater is the documented mechanism. If the calculation requires using a specific field’s value as part of a formula, such as a discount or amount, the field-based variable approach is the documented mechanism.

When defining your forecast inputs, check whether the source data is arranged in rows requiring positional lookup or if it is stored as individual field values on a record. If you are using a spreadsheet, verify that your index parameter is not smaller than 1, as this is an invalid input. If you are using a CRM formula, confirm that the field you have chosen is the correct variable for the calculation you intend to perform.

Calculation reference table and Pipedrive deal scope

The following table summarizes the documented calculation behaviors for the four tools compared in this guide. Each row reflects only the specific restrictions and capabilities cited in the respective publisher's support documentation.

PublisherCalculation TypeScopeKey Restriction
AirtableAggregate rollups and individual lookupsLinked recordsArray selections start at position one
Google SheetsRow lookupsSearch column and columns farther rightNumbers lookup columns from one
FreshsalesField-based formulasChosen field as variableMIN example considers twenty
PipedriveDeal formulasDeals onlyMonetary or numerical field required

For Pipedrive, the documentation specifies that formula values are calculated only in deals. The "Custom fields: formula fields - Knowledge Base | Pipedrive" page states that you must name the field and set its type to either monetary or numerical, according to Pipedrive. See Choose a deal field type: 4 systems and storage rules when you set that type. This requirement ensures that the input data is compatible with the arithmetic operations the formula performs. The same source notes that each company account can have up to ten formula fields, according to Pipedrive. This ceiling limits the number of distinct calculated fields available to the entire account.

After writing the formula, the user chooses whether it is applied to new deals or only new and open deals, according to Pipedrive. This selection determines the scope of records that will automatically update with the calculated value. For RevOps owners, this distinction matters when defining how calculated deal inputs enter the weekly forecast.

When defining your forecast inputs, verify that your Pipedrive formula fields are set to the correct data type. Check that the field type is monetary or numerical, as required by the documentation. Confirm the application scope matches your forecast needs, whether you require updates to new deals or new and open deals. Keep the total number of formula fields within the ten-field limit for your company account.

Which option fits linked totals, row matching or deal formulas

Choose the tool based on where the data lives. Linked-record aggregates belong in Airtable. Row-based lookups belong in Google Sheets. Deal-field formulas belong in Pipedrive or Freshsales. If your forecast input combines linked totals with deal-specific discounts, you may need to calculate the aggregate in Airtable and the discount in Pipedrive before merging them into your weekly number.

Read Forecast period rules: read 4 before the weekly number before you lock the weekly number.

Illustrative example of a small weekly total

Picture 4 deals. Two linked amounts are 40 and 10. Keeping the first item uses 40. Keeping the first 2 items uses 40 and 10. A lower-of check compares a discount with 20. A discount of 10 stays 10. A discount of 50 becomes 20.

Open the deal that feeds your weekly forecast. Mark which pattern it uses: linked records, a row lookup, a field variable, or a deal-only formula. Then check that pattern against the matching help page above before you write the weekly number.

FAQ: selecting a calculation for forecast inputs

Which sales calculation tools support linked totals and deal formulas?

Airtable supports aggregate calculations over linked records, as a rollup field performs aggregate calculations that leverage formula functions on specific cells from records that are linked together according to Airtable. Pipedrive supports deal formulas, where formula fields only calculate values in deals according to Pipedrive. Google Sheets and Freshsales describe row lookups or field-based formulas, with VLOOKUP searching in the search key column when index = 1 or columns that are further right according to Google.

Does Pipedrive calculate formula values on leads in my account?

No, Pipedrive formula fields only calculate values in deals according to Pipedrive.

How does Airtable distinguish a rollup from a lookup?

A rollup applies an aggregation formula, while a lookup field shows each linked record's value on its own instead of an aggregate according to Airtable.

What column positions can Google Sheets VLOOKUP return?

VLOOKUP can only search in the search key column, when index = 1, or columns that are further right according to Google.

How many formula fields can a Pipedrive company account have?

Each Pipedrive company account can have up to ten formula fields according to Pipedrive.

Sources