Working with Math and Trig Function Formulas
14 Mar 20235 minutes to read
Math and Trig Function Formulas includes the following functions:
- SUMIF
- SUMIFS
- SUMPRODUCT
- PRODUCT
SUMIF Function
SUMIF function to sum the values in a range that meet criteria that specify
The following code snippet illustrates on how to use SUMIF function formula.
// Create a new Excel Document.
final Workbook workbook = Workbook();
// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets[0];
// set the value to the cell.
sheet.getRangeByName('A1').setText('Apple');
sheet.getRangeByName('A2').setText('Grapes');
sheet.getRangeByName('A3').setText('Apple');
sheet.getRangeByName('A4').setText('Grapes');
sheet.getRangeByName('A5').setText('Apple');
sheet.getRangeByName('B1').setNumber(58);
sheet.getRangeByName('B2').setNumber(1200);
sheet.getRangeByName('B3').setNumber(300);
sheet.getRangeByName('B4').setNumber(500);
sheet.getRangeByName('B5').setNumber(1000);
sheet.getRangeByName('C1').setNumber(2);
sheet.getRangeByName('C2').setNumber(3);
sheet.getRangeByName('C3').setNumber(4);
sheet.getRangeByName('C4').setNumber(2);
sheet.getRangeByName('C5').setNumber(1);
// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();
// Setting formula in the cell.
Range range = sheet.getRangeByName('C8');
range.setFormula('=SUMIF(A1:A5,A2,B1:B5)');
range = sheet.getRangeByName('C9');
range.setFormula('=SUMIF(C1:C5,C4,B1:B5)');
// Save and dispose workbook.
final List<int> bytes = workbook.saveSync();
File('SUMIFFunction.xlsx').writeAsBytes(bytes);
workbook.dispose();
SUMIFS Function
SUMIFS function used to adds all of its arguments that meet multiple criteria.
The following code snippet illustrates on how to use SUMIFS function formula.
// Create a new Excel Document.
final Workbook workbook = Workbook();
// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets[0];
// set the value to the cell.
sheet.getRangeByName('A1').setText('Apple');
sheet.getRangeByName('A2').setText('Grapes');
sheet.getRangeByName('A3').setText('Apple');
sheet.getRangeByName('A4').setText('Grapes');
sheet.getRangeByName('A5').setText('Apple');
sheet.getRangeByName('B1').setNumber(58);
sheet.getRangeByName('B2').setNumber(1200);
sheet.getRangeByName('B3').setNumber(300);
sheet.getRangeByName('B4').setNumber(500);
sheet.getRangeByName('B5').setNumber(1000);
sheet.getRangeByName('C1').setNumber(2);
sheet.getRangeByName('C2').setNumber(3);
sheet.getRangeByName('C3').setNumber(4);
sheet.getRangeByName('C4').setNumber(2);
sheet.getRangeByName('C5').setNumber(1);
// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();
// Setting formula in the cell.
Range range = sheet.getRangeByName('C8');
range.setFormula('=SUMIFS(B1:B5,C1:C5,\">=2\")');
range = sheet.getRangeByName('C9');
range.setFormula('=SUMIFS(B1:B5,A1:A5,\"Apple\")');
// Save and dispose workbook.
final List<int> bytes = workbook.saveSync();
File('SUMIFSFunction.xlsx').writeAsBytes(bytes);
workbook.dispose();
SUMPRODUCT Function
SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.
The following code snippet illustrates on how to use SUMPRODUCT function formula.
// Create a new Excel Document.
final Workbook workbook = Workbook();
// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets[0];
// set the value to the cell.
sheet.getRangeByName('A1').setText('Apple');
sheet.getRangeByName('A2').setText('Grapes');
sheet.getRangeByName('A3').setText('Apple');
sheet.getRangeByName('A4').setText('Grapes');
sheet.getRangeByName('A5').setText('Apple');
sheet.getRangeByName('B1').setNumber(58);
sheet.getRangeByName('B2').setNumber(1200);
sheet.getRangeByName('B3').setNumber(300);
sheet.getRangeByName('B4').setNumber(500);
sheet.getRangeByName('B5').setNumber(1000);
sheet.getRangeByName('C1').setNumber(2);
sheet.getRangeByName('C2').setNumber(3);
sheet.getRangeByName('C3').setNumber(4);
sheet.getRangeByName('C4').setNumber(2);
sheet.getRangeByName('C5').setNumber(1);
// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();
// Setting formula in the cell.
Range range = sheet.getRangeByName('C8');
range.setFormula('=SUMPRODUCT(B1:B5,C1:C5)');
range = sheet.getRangeByName('C9');
range.setFormula('=SUMPRODUCT(--(A1:A5=\"Apple\"), B1:B5, C1:C5)');
// Save and dispose workbook.
final List<int> bytes = workbook.saveSync();
File('SUMPRODUCTFunction.xlsx').writeAsBytes(bytes);
workbook.dispose();
PRODUCT Function
PRODUCT function multiplies all the numbers given as arguments and returns the product.
The following code snippet illustrates on how to use PRODUCT function formula.
// Create a new Excel Document.
final Workbook workbook = Workbook();
// Accessing sheet via index.
final Worksheet sheet = workbook.worksheets[0];
// set the value to the cell.
sheet.getRangeByName('A1').setNumber(2);
sheet.getRangeByName('B1').setNumber(3);
sheet.getRangeByName('A2').setNumber(5);
sheet.getRangeByName('B2').setNumber(4);
sheet.getRangeByName('A3').setNumber(6);
sheet.getRangeByName('B3').setNumber(7);
sheet.getRangeByName('A4').setNumber(9);
sheet.getRangeByName('B4').setNumber(8);
// Formula calculation is enabled for the sheet.
sheet.enableSheetCalculations();
// Setting formula in the cell.
Range range = sheet.getRangeByName('C8');
range.setFormula('=PRODUCT(A1:A4,B1:B4)');
range = sheet.getRangeByName('C9');
range.setFormula('=PRODUCT(A1,B1)');
// Save and dispose workbook.
final List<int> bytes = workbook.saveSync();
File('PRODUCTFunction.xlsx').writeAsBytes(bytes);
workbook.dispose();