//create a new workbook var workbook = new GrapeCity.Documents.Excel.Workbook(); var sheet = workbook.Worksheets[0]; sheet.Name = "FILTER"; sheet.Range["A1"].Value = "The FILTER function filters a range or array based on criteria you specify. Syntax: FILTER(array,include,[if_empty])"; sheet.Range["B3:E19"].Value = new object[,] { { "Region", "Sales Rep", "Product", "Units" }, { "East", "Tom", "Apple", 6380 }, { "West", "Fred", "Grape", 5619 }, { "North ", "Amy", "Pear", 4565 }, { "South", "Sal", "Banana", 5323 }, { "East", "Fritz", "Apple", 4394 }, { "West", "Sravan", "Grape", 7195 }, { "North ", "Xi", "Pear", 5231 }, { "South", "Hector", "Banana", 2427 }, { "East", "Tom", "Banana", 4213 }, { "West", "Fred", "Pear", 3239 }, { "North ", "Amy", "Grape", 6420 }, { "South", "Sal", "Apple", 1310 }, { "East", "Fritz", "Banana", 6274 }, { "West", "Sravan", "Pear", 4894 }, { "North ", "Xi", "Grape", 7580 }, { "South", "Hector", "Apple", 9814 } }; sheet.Range["G3:L4"].Value = new object[,] { { "Criterion", "", "Product", "Units", "", "Total:" }, { 5000, null, null,null,null,null } }; ITable table = sheet.Tables.Add(sheet.Range["B3:E19"], true); ITable table1 = sheet.Tables.Add(sheet.Range["I3:J12"], true); table.ConvertToRange(); table1.ConvertToRange(); // Filter the data in the range B4:E19 where the Units(E4:E19) are greater than 5000(G4) sheet.Range["I4"].Formula2 = "=FILTER(D4:E19,E4:E19>G4,\"\")"; sheet.Range["L4"].Formula2 = "=SUM(IF(E4:E19>G4,1,0))"; sheet.Range["E4:E19,G4,J4:J12"].NumberFormat = "#,##0"; // Save to an excel file workbook.Save("FilterFunction.xlsx");
' Create a new Workbook Dim workbook As New Workbook Dim sheet = workbook.Worksheets(0) sheet.Name = "FILTER" sheet.Range!A1.Value = "The FILTER function filters a range or array based on criteria you specify. Syntax: FILTER(array,include,[if_empty])" sheet.Range("B3:E19").Value = New Object(,) { {"Region", "Sales Rep", "Product", "Units"}, {"East", "Tom", "Apple", 6380}, {"West", "Fred", "Grape", 5619}, {"North ", "Amy", "Pear", 4565}, {"South", "Sal", "Banana", 5323}, {"East", "Fritz", "Apple", 4394}, {"West", "Sravan", "Grape", 7195}, {"North ", "Xi", "Pear", 5231}, {"South", "Hector", "Banana", 2427}, {"East", "Tom", "Banana", 4213}, {"West", "Fred", "Pear", 3239}, {"North ", "Amy", "Grape", 6420}, {"South", "Sal", "Apple", 1310}, {"East", "Fritz", "Banana", 6274}, {"West", "Sravan", "Pear", 4894}, {"North ", "Xi", "Grape", 7580}, {"South", "Hector", "Apple", 9814} } sheet.Range("G3:L4").Value = New Object(,) { {"Criterion", "", "Product", "Units", "", "Total:"}, {5000, Nothing, Nothing, Nothing, Nothing, Nothing} } Dim table As ITable = sheet.Tables.Add(sheet.Range("B3:E19"), True) Dim table1 As ITable = sheet.Tables.Add(sheet.Range("I3:J12"), True) table.ConvertToRange() table1.ConvertToRange() ' Filter the data in the range B4:E19 where the Units(E4:E19) are greater than 5000(G4) sheet.Range!I4.Formula2 = "=FILTER(D4:E19,E4:E19>G4,"""")" sheet.Range!L4.Formula2 = "=SUM(IF(E4:E19>G4,1,0))" sheet.Range("E4:E19,G4,J4:J12").NumberFormat = "#,##0" ' save to an excel file workbook.Save("FilterFunction.xlsx")