# Set Array Formula

DsExcel allows you to set an array formula that executes multiple calculations on a cell or a range of cells to display a column or a row of subtotals.

## Content





Array formula is a formula that can execute multiple calculations on individual cells or a range of cells to display a column or a row of subtotals. The array formula can consist of array of row of values, column of values or simply a combination of rows and columns of values that may return either multiple results or a single result.

Array formulas can be used to simplify the following tasks in a worksheet:

1.  You can count the number of characters in a range of cells.
2.  You can sum numeric values in cells that meet a specified criteria. For instance,the highest value in a range or values that fall between an upper and lower boundary.
3.  You can sum every nth value in a range of cell values in a spreadsheet.

In DsExcel Java, you can use [setFormulaArray](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IRange.html#setFormulaArray) method of the [IRange](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IRange.html) interface to set array formula for a range. In case, you want to find out whether a range has array formula or not, you can use the [getHasArray](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IRange.html#getHasArray) method of the IRange interface. In order to get an entire array if specified range is part of an array, you can use [getCurrentArray](/document-solutions/java-excel-api/api/online/com/grapecity/documents/excel/IRange.html#getCurrentArray) method.

Refer to the following example code to set array formula and get entire array:

```Java
// Setting cell value using arrays
worksheet.getRange("E4:J5").setValue(new Object[][] { { 1, 2, 3 }, { 4, 5, 6 } });

worksheet.getRange("I6:J8").setValue(new Object[][] 
    {
        { 2, 2 }, 
        { 3, 3 }, 
        { 4, 4 }     
    });
        
// To set array formula for range 
// O P Q
// 2 4 #N/A
// 12 15 #N/A
// #N/A #N/A #N/A
        
worksheet.getRange("O9:Q11").setFormulaArray("=E4:G5*I6:J8");

// Verify if Range O9 has array formula.
if (worksheet.getRange("O9").getHasArray()) {
    
// Set Range O9's entire array's interior color.
    IRange currentarray = worksheet.getRange("O9").getCurrentArray();
    currentarray.getInterior().setColor(Color.GetGreen());
}
```