In this tutorial, we walk you through the creation of a custom formula using Google Apps Script in Google Sheets, allowing you to automatically calculate total costs for items and generate an overall expenditure.
Summary:
Managing calculations in spreadsheets becomes a breeze with this custom formula:
Step 1 – Setup:
- Create a new Google Sheets document.
- Enter item data in columns A, B, and C from row 2 onwards.
- Reserve column D for calculated costs and column E for the custom formula.
Step 2 – Writing Code:
- Click “Extensions,” then select “Apps Script.”
- Replace the default code with our script:
// Custom formula to calculate total cost for items and overall total function CALC_TOTAL_COST(items, quantities, prices) { var totalCost = 0; for (var i = 0; i < items.length; i++) { var cost = quantities[i] * prices[i]; totalCost += cost; items[i][3] = cost; } return totalCost; }
Step 3 – Using the Formula:
- Return to your Google Sheets document.
- In your desired cell (E6, for example), enter:lessCopy code
=CALC_TOTAL_COST(A2:A4, B2:B4, C2:C4)
Step 4 – Testing:
- Input item data in columns A, B, and C.
- Use the formula in your chosen cell to calculate the overall total cost.
Why This Matters:
- Simplify calculations: Automatically calculate individual item costs and the total expenditure.
- Efficient reporting: Streamline financial or inventory management tasks.
- Personalization: Adapt the formula to your specific needs.
- No coding experience required: Walk through our tutorial step by step.
By following this tutorial, you can harness the power of Google Apps Script to create a custom formula that saves you time and effort while enhancing your spreadsheet capabilities. Whether you’re tracking expenses or handling inventory, this technique will optimize your data processing.
Don’t forget to subscribe for more helpful tutorials on using technology to improve your workflow.
Get the total cost custom sheets formula calculate values across ranges
CALC_TOTAL_COST
| Item | Quantity | Price | Cost |
| Item 1 | 2 | 12 | 24 |
| Item 2 | 3 | 14 | 42 |
| Item 3 | 2 | 25 | 50 |
| Total Cost | 116 | ||
Scenario: You have a list of items with their quantities and prices in columns A, B, and C. You want to create a custom formula that calculates the total cost for each item (quantity * price) and provides the overall total of all items’ costs.
Here are the steps to achieve this:
Step 1: Setting up the Spreadsheet
- Create a new Google Sheets document.
- Enter your data in columns A, B, and C starting from row 2.
- Leave a blank cell in column D for the calculated cost for each item.
- Leave another blank cell in column E for the custom formula that calculates the total cost.
Data Table:
| A | B | C | D | E |
| Item | Quantity | Price | Cost | |
| Item 1 | 5 | 10 | ||
| Item 2 | 3 | 15 | ||
| Item 3 | 2 | 20 | ||
| Total Cost | cuntio |
Step 2: Writing the Google Apps Script Code
- Click on “Extensions” in the top menu, then select “Apps Script”.
- Delete any default code and replace it with the following script:
// Custom formula to calculate the total cost for each item and overall total
function CALC_TOTAL_COST(items, quantities, prices) {
var totalCost = 0;
for (var i = 0; i < items.length; i++) {
var cost = quantities[i] * prices[i];
totalCost += cost;
items[i][3] = cost;
}
return totalCost;
}
Step 3: Using the Custom Formula in Google Sheets
- Go back to your Google Sheets document.
- In a cell where you want the total cost to appear (let’s say cell E6), enter the following formula:
=CALC_TOTAL_COST(A2:A4, B2:B4, C2:C4)
function CALC_SUB(price,qty){
return price * qty;
}
function CALC_TOTAL_COST(items, quantities, prices) {
let totalCost = 0;
let output = ”;
for(let i=0;i<items.length;i++){
const cost = quantities[i] * prices[i];
output += `${items[i]} * ${quantities[i]}, `;
totalCost += cost;
}
return `${output} total cost is $${totalCost}`;
}
Explanation of the Code:
- The function CALC_TOTAL_COST takes three parameters: items, quantities, and prices.
- items: This parameter represents the range of cells containing the items’ names.
- quantities: This parameter represents the range of cells containing the quantities for each item.
- prices: This parameter represents the range of cells containing the prices for each item.
- Inside the function, we initialize the totalCost variable to keep track of the overall cost.
- The for loop iterates through each item. For each item, it calculates the cost by multiplying the quantity and price, and then adds this cost to the totalCost.
- Additionally, it updates the corresponding cell in column D (the “Cost” column) with the calculated cost for each item.
- The function returns the overall totalCost.
Step 4: Testing the Custom Formula
- Enter the items, quantities, and prices in columns A, B, and C starting from row 2.
- Use the custom formula in cell E6 to calculate the overall total cost of all items.
For example, if you have entered the data as shown in the data table, the calculated total cost in cell E6 should be 110.
Remember to enable the “Google Apps Script” extension and use the exact function name (CALC_TOTAL_COST) in your formula.
CALC_SUB(price, qty): This function takes in two parameters, price and qty (quantity). It calculates the subtotal cost of a product by multiplying the given price with the given quantity. The function simply returns the result of this multiplication.
function CALC_SUB(price,qty){
return price * qty;
}
CALC_TOTAL_COST(items, quantities, prices): This function is more complex and takes three arrays as parameters: items, quantities, and prices. It calculates the total cost for a list of items based on their quantities and prices. The function iterates over each item using a for loop and calculates the cost of each item by multiplying its quantity with its corresponding price.
The function maintains two variables: totalCost to keep track of the cumulative cost of all items, and output to store a string that records the calculation for each item.
Inside the loop, the cost of the current item is calculated using quantities[i] * prices[i], and then the item’s description along with its quantity is added to the output string. The calculated cost is also added to the totalCost.
After iterating through all items, the function returns a string containing the calculations for each item and the total cost.
function CALC_TOTAL_COST(items, quantities, prices) {
let totalCost = 0;
let output = ”;
for(let i=0;i<items.length;i++){
const cost = quantities[i] * prices[i];
output += `${items[i]} * ${quantities[i]}, `;
totalCost += cost;
}
return `${output} total cost is $${totalCost}`;
}
// Output: “Apple * 5, Banana * 3, Orange * 2, total cost is $7.1”
The CALC_TOTAL_COST function is designed to calculate the total cost of purchasing multiple items with their respective quantities and prices. The function returns a formatted string that includes both the item calculations and the total cost.