Google-sheets – How to collect the sum of different columns while filtering

google sheets

I am creating a travel budget spreadsheet and need to be able to sum different filters (By Month, By Salesperson, By State, etc)…is there a way for me to display the sum of a column based on what is displayed in variable filters?

I believe I solved this. I ended up just pulling each person's travel into a different sheet on a new document and having multiple tabs (one for each staff member). Thank you for all your help!

Best Answer

You should be using a pivot table (select A:O, Data > Pivot table report... and add fields of your choice).

Maybe Primary Attendee in Rows, Month in Columns, Fee in Values and State in Filter - but the options are almost limitless (that's the power of a pivot table).

Related Solutions

Google Sheets – How to Refer to Two Different Columns in a COUNTIF Function

Use a combination of the ARRAYFORMULA, SUM and arithmetic operations

Here's the function to count all of the clicks by a 26 year old:

=ARRAYFORMULA(sum((A:A=26) * (C:C="true")))

There are 3 parts to this operation.

The ARRAYFORMULA takes care of looping over the specified range
The SUM manages counting all of the true results

Essentially, true is being converted to 1 and false is being converted to 0. Boolean logic is done by using arithmetic operations.

An AND operation uses multiplication:

(1 * 1) = 1 - (True && True) = True
(1 * 0) = 0 - (True && False) = False
(0 * 0) = 0 - (False && False) = False

An OR operation uses a combination of the *SIGN function and addition:

sign(1 + 1) = 1 - (True || True) = True
sign(1 + 0) = 1 - (True || False) = True
sign(0 + 0) = 0 - (False || False) = False

Note: The sign function is necessary because of the way boolean addition works differently than arithmetic addition. Basically in boolean addition 1 + 1 = 1, in arithmetic addition 1 + 1 = 2. Obviously, arithmetic addition will mess up the count so you need to run the results of the addition operations through a sign function. The sign function returns 1 if the value is positive, 0 if the value is 0, and -1 if the value is negative.

Lets say you wanted to count the clicks for all users between age 20-25:

=ARRAYFORMULA(sum(sign((A:A=20) + (A:A=21) + (A:A=22) + (A:A=23) + (A:A=24) + (A:A=25)) * (C:C="true")))

Google Sheets – How to Sum Throughout Different Sheets

You can use the following script to get the sum from multiple sheets:

UPDATE
Because the script is only executed at an interval of 5 minutes, and an OnEdit trigger won't work, I had to revise the answer I gave in order for it to be practical. See revision history to notice the differences.

The first part

will set the spreadsheet and create an extra menu option, called Sum:

var ss = SpreadsheetApp.getActiveSpreadsheet();

function onOpen() {
  var menu = [{name: "get sum", functionName: "sumSheets"}];
  ss.addMenu("Sum", menu);  
}

The second part

will ask for a starting position and collect the total number of sheets and will determine the position of the Result sheet (not sheet ID but the absolute position). After which it will open each consecutive sheet starting from the first parameter (zero based !!), to gather the values and add them to the sum, till it hits the Result sheet. At the end, the total sum is added to any active cell:

function sumSheets() {
  var pos = Browser.inputBox("Where do you want to start (zero based)?"); 
  var sh = ss.getNumSheets();

  for(var k=0; k<sh; k++) {
    if(ss.getSheets()[k].getName() == "Result") {
      var ref = k;
    }
  }

  var sum=0;
  for(var i=pos; i<ref; i++) {
    this.i = ss.getSheets()[i];
    var data = this.i.getRange(2, 1, this.i.getLastRow(), 1).getValues();
    for(var j=0, len=data.length; j<len; j++) {
      sum += Number(data[j]);
    }    
  }

  ss.getActiveCell().setValue(sum); 

  return;
}

To trigger the calculation, press get sum from the menu option Sum and the result will be added.

See example file I've prepared: SUM throughout multiple sheets