Posted by: zhenghq on 8 September 2017, 2:48 pm EST

• Post Options:

Posted 8 September 2017, 2:48 pm EST

I use this formula

max(h1:h720), countif((h1:h720),0),sum(h1:h720) ......

but the number of 720 is not fixed, it's changable according to the database record. As the record increase or decrease, it changes

in Excle,I use an enough large number to solve this problem. For example, my total database record at that time is 500, my Excel2003 formula is max(h1:h65000)

so how to write the formula in Spread.

• Post Options:

Replied 8 September 2017, 2:48 pm EST

zheng_hq,

If your want to include all values in the column in the formula then you can use a column reference. For example, the formula SUM(H:H) adds up all the values in column H. Column references are supported in both Spread and Excel.

• Post Options:

Replied 8 September 2017, 2:48 pm EST

bobbyo:

I use FrozenTrailingRows instead of ColumnFooter

IF fill the cells in FrozenTrailingRows by using formula SUM(H:H), it can't show the computation.

so how to use sum(h:h) in ColumnFooter

• Post Options:

Replied 8 September 2017, 2:48 pm EST

Hello,

As per your requirement you want to sum all the values in a column, regardless of the number of values in the column, and display the result in ColumnFooter or FrozenTrailingRow. In case you want to display the sum in ColumnFooter you can use the SetAggregationType method which would sum all the values in a Column regardless of the count and display the result in ColumnFooter. The code to implement the same is:

Private Sub button1_Click(sender As Object, e As EventArgs)
End Sub

This piece of code would sum all the values in first column and display the result in the first cell of the first row in the ColumnFooter.

Secondly, if you want to display the sum in FrozenTrailingRow, then you can make use of a local variable, to get the total number of rows in the sheet and use that variable in your formula to specify the index of the last row in the cell range specified in the formula, the code for implementing the same is:

Dim num As Integer = fpSpread1.Sheets(0).RowCount - 2
Private Sub button1_Click(sender As Object, e As EventArgs)
End Sub

This piece of code would sum all the values in the first column and display the result in first cell of the first FrozenTrailingRow.

Thanks,

Manpreet Kaur.

##### Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

• #### ComponentOne

Forums for all current editions of the ComponentOne .NET UI control product line, including ComponentOne Studio and ComponentOne Studio for Xamarin.

• #### ActiveReports

Forums for all versions of ActiveReports and ActiveReports Server