budget.js

function totalAcrossSheets(){
  // todo: only run if we edit city sheets, column 4. ignore otherwise.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  var edited_sheet = ss.getActiveSheet();
  var edited_cat = edited_sheet.getRange(edited_sheet.getActiveCell().getRow(), 1).getValue();
  var edited_subcat = edited_sheet.getRange(edited_sheet.getActiveCell().getRow(), 2).getValue();

  var cities = [
    "city1", 
    "city2",
    "city3",
    "city4"
  ];
  
  var subtotal = 0;
  for(var i=0; i<cities.length; i++) {
    var city_sheet = ss.getSheetByName(cities[i]);
    var city_rows = city_sheet.getMaxRows();
//    var city_data = city_sheet.getRange(1, 1, city_rows, 4);

    var cat_range = city_sheet.getRange(1,1,city_rows).getValues(); // this one takes 10 seconds
    var subcat_range = city_sheet.getRange(1,2,city_rows).getValues();
    var value_range = city_sheet.getRange(1,4,city_rows).getValues();
    
    for(var j=0;j<city_rows;j++){
      if(cat_range[j] == edited_cat) { // cat match
        if(subcat_range[j] == edited_subcat) { // subcat match
          subtotal += parseFloat(value_range[j]);
          j = city_rows;
        } // matched subcat
      } // matched cat
    } // row loop
  } // city loop
  
  var budget = ss.getSheetByName("Budget");    
  var budget_max = budget.getMaxRows();
  Logger.log('budget cat getValues');
  var categories = budget.getRange(1, 1, budget_max).getValues();
  Logger.log('budget subcat getValues');
  var subcategories = budget.getRange(1, 2, budget_max).getValues();
  
  for(var k=0; k<budget_max; k++){
    if(categories[k] == edited_cat){
      if(subcategories[k] == edited_subcat){
        budget.getRange(k+1, 4).setValue(subtotal);
        k=budget_max;
      } // subcat range
    } // cat match
  } // budget loop
  return true;
}