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
.