Working with Math and Trig Function Formulas

8 Apr 20215 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.

  • dart
  • // 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.saveAsStream();
    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.

  • dart
  • // 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.saveAsStream();
    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.

  • dart
  • // 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.saveAsStream();
    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.

  • dart
  • // 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.saveAsStream();
    File('PRODUCTFunction.xlsx').writeAsBytes(bytes);
    workbook.dispose();