Conditional sum formula

Conditional sum is a function that adds up the values in a specified range based on certain conditions being met. This is a powerful tool for analyzing data and creating customized reports. For example, you may want to calculate the total sales for a particular product or region. With conditional sum, you can specify the criteria and quickly generate the desired results.

Here is an example of a custom formula script to perform a conditional sum in Google Sheets using Apps Script:

function conditionalSum(range, criteriaRange, criteria) {
  var sum = 0;
  var data = SpreadsheetApp.getActiveSheet().getRange(range).getValues();
  var criteriaData = SpreadsheetApp.getActiveSheet().getRange(criteriaRange).getValues();
  for (var i = 0; i < data.length; i++) {
    if (criteriaData[i][0] == criteria) {
      sum += data[i][0];
  return sum;

This function takes in three parameters: range, criteriaRange, and criteria. The range parameter specifies the range of cells to sum up. The criteriaRange parameter specifies the range of cells that contains the criteria to be met. The criteria parameter specifies the criteria to be met.

To use this custom formula, you can enter it into a cell and provide the necessary parameters. For example, if you wanted to calculate the sum of sales for a specific product in a certain region, you would enter the following formula into a cell: =conditionalSum("C2:C10", "A2:A10", "ProductA"). This would calculate the sum of values in the range C2:C10 where the corresponding value in the range A2:A10 is equal to ProductA.