how do I get Add and Sum by column index value in Google Spreasheet -

how do I get Add and Sum by column index value in Google Spreasheet -

i have sheet, it's called raw sheet this:

column index | | b | david | 1 | 10 | jerry | 5 | 15 | david | 1 | 50 | jerry | 6 | 20 | david | 8 | 20 |

there limited values in column index. in case "david" , "jerry".

i want create sheet, it's called summary sheet can summarize value column index value, this:

column index summary | f(a,b) | david | value | jerry | value |

the f(a,b) can kind of function take utilize of values in first sheet. 1 example: add together every row's a*b new number. in case, be:

column index summary | f(a,b) | david | 220 | 1*10 + 1*50 + 8*20 jerry | 195 | 5*15 + 6*20

what should do?

here's google sheets custom function you. operate on arbitrary table of numerical data, aggregating numbers arbitrary (but simple) algebraic expression. (expression must valid in javascript, e.g. "a * b", "a + b / c", or "math.pow(a,b)".) there's no error checking, it's not fool-proof.

examples:

=summary('raw sheet'!a1:c6,"a*b") yes, can refer different sheets.

=summary(a1:c6,"a*a + b")

=summary(a1:c6,"math.pow(a,b)")

custom function /** * performs given formula on each row in table, aggregating (summing) * row results key value in first column. * * see: http://stackoverflow.com/questions/26925283/how-do-i-get-add-and-sum-by-column-index-value-in-google-spreasheet/26942156#26942156 * * @param {range} table input info table, including headers * @param {string} formula mathematical function peform on each * row in table, using header values * function parameters. * @param {int} sorttype (optional, default 1) 0: not sort, 1: sort ascending, -1: sort descending * @param {int} sortcolumn (optional, default 1) column sort by. * * @return {range} summary table of results * @customfunction */ function summary(table,formula,sorttype,sortcolumn) { sorttype = (sorttype == undefined) ? 1 : sorttype; sortcolumn = (sortcolumn == undefined) ? 1 : sortcolumn; // sort comparing function ordering summary table // uses sorttype & sortcolumn function colcompare(a,b) { var col = sortcolumn - 1; var order = sorttype; if (!order) homecoming 1; else homecoming ((a[col] < b[col]) ? -order : ((a[col] > b[col]) ? order : 0)); } var headers = table[0]; // start results header row var summarytable = [[headers[0],string(formula)]]; // evaluate formula, replacing variables (headers) references table (var h = 1; h < headers.length; h++) { var re = new regexp(headers[h],"g"); formula = formula.replace( re, " table[row]["+parseint(h)+"] " ); } // aggregate info summing formula each row var summary = {}; (var row=1; row<table.length; row++) { var key = table[row][0]; if (!(key in summary)) summary[key] = 0; summary[key] += eval( formula ); } // append aggregated rows results, , homecoming (key in summary) { summarytable.push([key,summary[key]]); } // sort results headers = summarytable.splice(0, 1); summarytable.sort(colcompare).unshift(headers[0]); homecoming summarytable; }

edit: nov 17 - added sort functionality

google-apps-script google-spreadsheet