Summing up array values with if statements link to Google Sheet data

How To
1

I am creating an invoice template with a Google Doc that contains dynamic fields. I am stuck trying to sum up an array that is multiplying time by rate. I have used parseNumber to make sure the data is numeric only. The times are different on every row. The rate varies between a standard rate and an express rate. The rates are linked to a Google Sheet CRM and associated with the specific client. I have spent a few hours (and credits) trying to get it to work and all I get is £0. Can anyone please help? Thank you in advance :slight_smile:

total_exvat
total_exvat594×713 48.6 KB

2

Hey Jonathan,

do you need to multiply each item in the array by a number? Can you share screenshots of your flow and the input bundles?

3

Hi Stoyan,

Thanks for responding to my post. I’m quite new to make.com and generating functions etc.

Yes I need to multiple a different times by different rates in the row and get the total.

10.Array ; 5 checks whether it’s the express rate or not (not express = 0 and express = 1)
10.Array ; 6 gets the time

1.5 hours x £10 (not express rate) = £15
2 hours x £20 (express rate) = £40
1 hour x £10 (not express rate) = £10

total_exvat = £65

image
image2330×498 115 KB

Input Bundle to Microsoft Word Templates.txt (14.1 KB)

4

I checked the incoming data and its quite tangled. From what I can see you have values.8.value.1.entry.5 for the Rate - this is the one you are checking first right? But in the bundle you shared, they are 40 or 27, not 1 or 0 as in your explanation. Then you need to multiply the value in values.8.value.1.entry.6 which is the time by the corresponding value coming from the other sheet right?

5

Hello Stoyan,

Thanks again for getting back to me.

I am pulling through 4 bundles based on specific criteria (client ID and within a date range).

The Microsoft Word Templates module keeps adding new rows within a table for every items that needs to be invoiced. This is done via a looping value type. The data is taken from the previous iterator module.

I now need to calculate the totals for the rows by pulling data from the aggregator module and using functions / formulas. My reference to the ’ 0 = no express and 1 = express ’ is due to the output from the bundle. In my G Sheet there is a tick box for signalling whether the time should be charged at the express rate or not. Rather than having a value of true or false, I changed it to 0 or 1.

Unticked (standard rate) = 0
Ticked (express rate) = 1

  1. Array ; 5 contains the data from the tick box

Therefore I decided to use the IF( ‘express rate’ = 0; output ‘standard rate’; alternatively output ‘express rate’) and then multiple it by the hours in the array. All of this is then placed inside a SUM () to gather the total

I have tried writing this so many different ways that my head is about to explode! I love Make.com and the deeper I go, the more useful it becomes. It just gets a bit challenging when you don’t have a tech background.