Multiplying values from two line items

  • 9 January 2020
  • 4 replies
  • 202 views

Userlevel 1

Hi all,

We are creating a log of accepted offers with total revenue. Our CRM contains products with services that have different hourly rates. This data is pushed as follows:

line item A: service 1, service 2, service 3, ...

line item B: hourly rate 1, hourly rate 2, hourly rate 3, ...

line item C: amount of hours 1, amount of hours 2, amount of hours 3, ...

Unfortunately total revenue is not included so we want to calculate this by doing the following calculation:

(hourly rate 1 * amount of hours 1) + (hourly rate 2 * amount of hours 2) + (hourly rate 3 * amount of hours 3)

The amount of services is not equal per product. How would we do this?



This topic has been closed for comments

4 replies

Userlevel 7
Badge +11

@PaulKortman is right that Google Sheets is a good first step to try here.

Are you exporting the information from your CRM, or is it an app that Zapier integrates with? In other words, will you be using a new product/service in the CRM as the trigger for a Zap or does all of the data already exist?

If you're using a new product/service as a trigger then as Paul said, you'll want to use the Google Sheets Create Spreadsheet Row(s) trigger. This will put each line item on a separate row in the sheet.

I hope that's clear, let us know if you have any questions!


Userlevel 7
Badge +9

I'm not sure if I'm visualizing the data correctly, so are you saying that if you used a Google Sheets create row(s) step and put line item A in column A and line item B in column B etc you would end up with a spreadsheet like this:

Screenshot 2020-01-09 11.57.31.pngOr a spreadsheet like this:

Screenshot 2020-01-09 11.58.50.pngI'm assuming the first.


If so you could put it into a spreadsheet row like that and have the 4th column be a formula. see the below help article for how to put formulas into a spreadsheet row:

Google Sheets - Integration Help & Support | Zapier Then you simply look up the row in another step and it will have the result of the formula for you to use later in the Zap.


This is just one of the possible ways to do this.


Userlevel 7
Badge +9

@Robert_Segelquist a spreadsheet-style formula doesn't play nice with line items as it passes the comma separated values to the spreadsheet style formula, not the individual line items. I wish that weren't the case, because I could use spreadsheet-style formulas much more often, but because of it's lack of line item support/handling it doesn't work.

Say you had two values, rate, number of hours works in line items:

  • Service 1 $40/hour 4 hours
  • Service 2 $50/hour 5 hours

Passing those two values (rate and hours) to the spreadsheet like formula of (rate*hours) would provide the following to the spreadsheet like formula: (40,50*4,5) which would error because it doesn't know how to handle the commas.


Userlevel 2
Badge

I'd try a spreadsheet style formula as you have it keyed out in your comment, this would avoid the need of using Google sheets.