Google Sheets – How to Calculate Variance with Criteria

The following little script will calculate the median at the same time.

Code

function myMedian(instances, values) {
  var ins = [], half;
  for(var i = 0, iLen = instances.length; i < iLen; i++) {
    for(var k = 0, kLen = instances[i]; k < kLen; k++) {
      ins.push(Number(values[i]));
    }
  }

  // reference: https://gist.github.com/caseyjustus/1166258
  half = Math.floor(ins.sort( function(a,b) {return a - b;} ).length / 2);

  return ins.length % 2 ? ins[half] : (ins[half - 1] + ins[half]) / 2;
}

Screenshot

Example

See example file I prepared: Median calculation

Please try, in B2 of Expense_Overview:

=sumif(Expense_Detail!D:D,A2,Expense_Detail!C:C)  

copied down to suit, or:

=ArrayFormula(sumif(Expense_Detail!D:D,A2:A,Expense_Detail!C:C))

You have a treat in store whenever you get around to pivot tables. They are very powerful but incredibly easy to use for all that:

Select A1:D7 in Expense_Detail, Data, Pivot table report..., for Rows Add field select Expense Category, for Values Add field select Amount.

PS Remove trailing space from A3 in Expense_Overview.

PPS If a pivot table will do the job it is normally far and away the best (and often easiest!) solution.