Skip to content

Instantly share code, notes, and snippets.

@willjobs
Created October 2, 2017 03:11
Show Gist options
  • Save willjobs/2b16c9464f1975421e4cf21895706c06 to your computer and use it in GitHub Desktop.
Save willjobs/2b16c9464f1975421e4cf21895706c06 to your computer and use it in GitHub Desktop.
Custom function for Google sheets to sum all of the dollar amounts written in notes in a given cell. If given a range of cells, will calculate the sums for each cell independently and output them below the cell with the formula.
function sumdollars(input) {
if(input.map) {
return input.map(sumdollars);
} else {
if(input.length === 0) {return 0;}
if(typeof input === 'number') {return input;}
var strArr = input.split('$').slice(1);
var mysum = 0;
for(i=0; i < strArr.length; i++) {
mysum += parseFloat(strArr[i].split(/[^0-9\.]+/)[0]);
}
return mysum;
}
}
@willjobs
Copy link
Author

willjobs commented Oct 2, 2017

Google Sheets Custom Function: Sum Dollars

Custom function for Google sheets to sum all of the dollar amounts written in notes in a given cell. If given a range of cells, will calculate the sums for each cell independently and output them below the cell with the formula.

How to use

Step 1: Open a Google Sheets file (or create a new one). Then go to Tools --> Script Editor.
Step 2: Paste the code below.
Step 3: Use the formula in a cell in your spreadsheet by supplying the function with another cell (or range of cells). If a range of cells is supplied to the function, the function will output a value for each cell in the range (as documented here: https://developers.google.com/apps-script/guides/sheets/functions#optimization).

Code

function sumdollars(input) {
  if(input.map) {
    return input.map(sumdollars);
  } else {
    if(input.length === 0) {return 0;}
    if(typeof input === 'number') {return input;}
    var strArr = input.split('$').slice(1);
    var mysum = 0;
    for(i=0; i < strArr.length; i++) {
      mysum += parseFloat(strArr[i].split(/[^0-9\.]+/)[0]);
    }
  
    return mysum;
  }
}

Examples

Example 1

Cell A1:

hostel - $8
food/drink - $20
bus to Bangkok - $27

Cell B1: =sumdollars(A1)

Result:

Cell B1: $55

Example 2

Cell A1:

hostel - $8
food/drink - $20
bus to Bangkok - $27

Cell A2:

food - $10

Cell A3:

visit to Park - $20
bus - $3

Cell B1: =sumdollars(A1:A3)

Result:

Cell B1: $55
Cell B2: $10
Cell B3: $23

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment