## Need Custom formula

Posted by: mohanmuthusamy on 8 September 2017, 1:12 pm EST

• Post Options:

Posted 8 September 2017, 1:12 pm EST

Employer 1
HRA and Other Allowances        3000
HRA and Other Allowances        4000

Total Salary from Employer 1        7000
Employer 2
HRA and Other Allowances        5000
Fully Exempt Cash Allowance        6000

Total Salary from Employer 2        11000
Employer 3
HRA and Other Allowances        7000
Fully Exempt Cash Allowance        3000

Total Salary from Employer 3        10000
Employer 4
Fully Exempt Cash Allowance        1000
Fully Exempt Cash Allowance        4000

Total Salary from Employer 4        5000
Employer 5
HRA and Other Allowances        2000
Fully Exempt Cash Allowance        3000

Total Salary from Employer 5        5000
Total Salary:

We see Above . i want to <b>add </b> Total Salary from Employer 1 +Total Salary from Employer 2 +Total Salary from Employer 3 +Total Salary from Employer 4 + Total Salary from Employer 5
Total Salary from Employer is dynamic row in future there may come  Total Salary from Employer 6,7 like. But my query is add all the Total Salary from Employer  and put it in total salary. I tried in many formauls by using SUMIF, mid functions... please help me.....

• Post Options:

Replied 8 September 2017, 1:12 pm EST

Dear Scotts,
I tried this one in farpoint 2.5 version. we convert all coding to c#. But in this part
`<span class="kwd"> Public Overloads Overrides Function</span> Evaluate(<span class="kwd">ByVal</span> args() <span class="kwd">As Object</span>) <span class="kwd">As Object    Dim</span> total <span class="kwd">As Integer    Dim</span> lbl <span class="kwd">As String</span>    total = 0    <span class="kwd">For</span> i <span class="kwd">As Integer</span> = args(0).Row <span class="kwd">To</span> args(0).Row + args(0).Row + args(0).RowCount - 1      lbl = Sheet.GetValue(i, 0)      <span class="kwd">If Not</span> lbl <span class="kwd">Is Nothing AndAlso</span> lbl.ToString.StartsWith(<span class="st">"Total Salary from"</span>) <span class="kwd">Then</span>        total += Sheet.GetValue(i, 1)      <span class="kwd">End If    Next    Return</span> totalEnd Function      In this Part (args[0].Row ) . Row is not there.. please convert this one into c# and help me kindly.<span class="kwd"></span>`

• Post Options:

Replied 8 September 2017, 1:12 pm EST

Dear Bobbyo,
• Post Options:

Replied 8 September 2017, 1:12 pm EST

Hello,

You should be able to debug this function in the custom class and see what type of object is passed into args[0] and then cast the object to that type. That type will have a Row property you can use.

• Post Options:

Replied 8 September 2017, 1:12 pm EST

• Post Options:

Replied 8 September 2017, 1:12 pm EST

mohanmuthusamy,

You could use the SUBTOTAL function.  If the cell range passed to the SUBTOTAL function contains subtotals (i.e. nested subtotals) then these nested subtotals are ignored to avoid double counting.

An example with two employers might look like...

sheet.Cells[0, 0].Value = "Employer1";
sheet.Cells[1, 1].Value = 3000.0;
sheet.Cells[2, 1].Value = 4000.0;
sheet.Cells[3, 1].Formula = "SUBTOTAL(9,B2:B3)";

sheet.Cells[4, 0].Value = "Employer1";
sheet.Cells[5, 1].Value = 5000.0;
sheet.Cells[6, 1].Value = 6000.0;
sheet.Cells[7, 1].Formula = "SUBTOTAL(9,B6:B7)";

sheet.Cells[9, 0].Value = "Total";
sheet.Cells[9, 1].Formula = "SUBTOTAL(9, B1:B9)";

• Post Options:

Replied 8 September 2017, 1:12 pm EST

Hello,

Here is a way to implement this in a custom function.

`  <SPAN class=kwd>Private Sub</SPAN> Form1_Load(<SPAN class=kwd>ByVal</SPAN> sender <SPAN class=kwd>As</SPAN> System.<SPAN class=kwd>Object</SPAN>, <SPAN class=kwd>ByVal</SPAN> e <SPAN class=kwd>As</SPAN> System.EventArgs) <SPAN class=kwd>Handles MyBase</SPAN>.Load    FpSpread1.Sheets(0).AddCustomFunction(<SPAN class=kwd>New</SPAN> mySum(FpSpread1.Sheets(0)))    FpSpread1.Sheets(0).Cells(25, 1).Formula = <SPAN class=st>"MySum(A1:B25)"</SPAN>  <SPAN class=kwd>End SubPublic Class</SPAN> mySum  <SPAN class=kwd>Inherits</SPAN> FarPoint.CalcEngine.FunctionInfo  <SPAN class=kwd>Dim</SPAN> Sheet <SPAN class=kwd>As</SPAN> FarPoint.Win.Spread.SheetView  <SPAN class=kwd>Public Sub New</SPAN>(<SPAN class=kwd>ByVal</SPAN> sv <SPAN class=kwd>As</SPAN> FarPoint.Win.Spread.SheetView)    Sheet = sv  <SPAN class=kwd>End Sub  Public Overloads Overrides Function</SPAN> Evaluate(<SPAN class=kwd>ByVal</SPAN> args() <SPAN class=kwd>As Object</SPAN>) <SPAN class=kwd>As Object    Dim</SPAN> total <SPAN class=kwd>As Integer    Dim</SPAN> lbl <SPAN class=kwd>As String</SPAN>    total = 0    <SPAN class=kwd>For</SPAN> i <SPAN class=kwd>As Integer</SPAN> = args(0).Row <SPAN class=kwd>To</SPAN> args(0).Row + args(0).Row + args(0).RowCount - 1      lbl = Sheet.GetValue(i, 0)      <SPAN class=kwd>If Not</SPAN> lbl <SPAN class=kwd>Is Nothing AndAlso</SPAN> lbl.ToString.StartsWith(<SPAN class=st>"Total Salary from"</SPAN>) <SPAN class=kwd>Then</SPAN>        total += Sheet.GetValue(i, 1)      <SPAN class=kwd>End If    Next    Return</SPAN> total  <SPAN class=kwd>End Function  Public Overrides Function</SPAN> AcceptsReference(<SPAN class=kwd>ByVal</SPAN> i <SPAN class=kwd>As Integer</SPAN>) <SPAN class=kwd>As Boolean    Return True  End Function  Public Overrides ReadOnly Property</SPAN> MaxArgs() <SPAN class=kwd>As Integer    Get      Return</SPAN> 1    <SPAN class=kwd>End Get  End Property  Public Overrides ReadOnly Property</SPAN> MinArgs() <SPAN class=kwd>As Integer    Get      Return</SPAN> 1    <SPAN class=kwd>End Get  End Property  Public Overrides ReadOnly Property</SPAN> Name() <SPAN class=kwd>As String    Get      Return</SPAN> <SPAN class=st>"mySum"</SPAN>    <SPAN class=kwd>End Get  End PropertyEnd Class</SPAN>`

• Post Options:

Replied 8 September 2017, 1:12 pm EST

Hello,

Could you please provide more details related to the issue? Are you using the Windows application or web application? The AddCustomFunction is valid member of both the Spread for Windows as well as Web application and it can be accessed as follows in a web application:

<BLOCKQUOTE>

<FONT class=keyword>Private</FONT> <FONT class=keyword>Sub</FONT> PageLoad(<FONT class=keyword>ByVal</FONT> sender <FONT class=keyword>As</FONT> System.<FONT class=keyword>Object</FONT>, <FONT class=keyword>ByVal</FONT> e <FONT class=keyword>As</FONT> System.EventArgs) Handles MyBase.Load

<FONT class=keyword>Dim</FONT> dataModel <FONT class=keyword>As</FONT> <FONT class=keyword>New</FONT> FarPoint.Web.Spread.Model.DefaultSheetDataModel(5, 5)
dataModel.SetFormula(1, 1, <FONT class=string>"CUBE(4)"</FONT>)

<FONT class=keyword>End</FONT> <FONT class=keyword>Sub</FONT>

</BLOCKQUOTE>

When do you get  the above mentioned message, how are you using the Spread control at your side? Thanks

##### 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 Developer and Server

• #### Wijmo

Forums for all Wijmo products, including Wijmo Core, FinancialChart, FlexSheet, MultiRow, OLAP, and ReportViewer

• #### Document APIs

Forums for all versions of GrapeCity Documents for Excel and GrapeCity Documents for PDF

• #### Archives

Forums for GrapeCity legacy products, including Xuni and ActiveAnalysis.