Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Subtracting multiple fields from a total

Topic Labels: Formulas
384 1
Showing results for 
Search instead for 
Did you mean: 

Here’s one for you.
We have a purchase order for a job, with a total amount in one table.

We have multiple work shifts each be a varied amount depending on hours and rates of pay in another table.

I’d like to be able to summarise all shifts and subtract them from the total purchase order to check we are under budget.

Is this possible?

1 Reply 1

Yes, this is possible, assuming that everything is joined with linked records.

I am picturing the following tables:

  • Job/Purchase Order (linked to Shifts)
  • Shifts (linked to Job/Purchase Order and Pay Rate)
  • Pay Rates
  1. In the Shift table, rollup the Pay Rate.
  2. Have the Shift calculate its amount based on its hours and pay rate.
  3. In the Job/Purchase Order, rollup the shift amounts. (SUM(values))
  4. In the Job/Purchase Order, subtract the rolledup shift amounts from the total.