How to do sum() on a particular column in spread sheet

How to do sum() on a particular column in spread sheet

Posted by: veera6681 on 8 September 2017, 5:24 am EST

• 

  veera6681

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:24 am EST

  i have integer field in one spread sheet column.. in coding i need to make sum of all cell of that column.. and also i need to handle the null values..

  how to do it in c# windows application. 
  
  

   

• 

  reeva

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:24 am EST

  Hello Veera,

  In case you are using Spread for Win Forms version 5.0.XX then you can use SetAggregationType method of the ColumnFooter and set its AggregationType to Sum to get the Sum of that Column in columnFooter and it treat the null cells as cells with value 0, i.e. by default cells in that column has value 0.

   fpSpread1.Sheets[0].ColumnFooter.Visible = true;
  
   fpSpread1.Sheets[0].ColumnFooter.SetAggregationType(0, 0, FarPoint.Win.Spread.Model.AggregationType.Sum);

  And in case you are using spread for WinForms version 4.0.XX then you can use the code given below to get the sum of the column in footer by setting FrozenTrailingRowCount to1, which will behave as ColumnFooter. Here is the code to achieve the same:-

              fpSpread1.ActiveSheet.Rows.Count = 10;
  
              fpSpread1.ActiveSheet.FrozenTrailingRowCount = 1;
  
              int rowCount = fpSpread1.ActiveSheet.Rows.Count - 1;
  
              fpSpread1.ActiveSheet.Cells[fpSpread1.ActiveSheet.Rows.Count - 1, 0].Formula = "Sum(A1:A" + rowCount + ")";

  Hope this will help you. Thanks.

• 

  chris-ong

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:24 am EST

  Hello,

  I have a column containing the labor units expressed in minutes.  I used the following to sum this column:

  fpSpread1.Sheets[0].ColumnFooter.Visible = true;
  
  fpSpread1.Sheets[0].ColumnFooter.SetAggregationType(0, 0, FarPoint.Win.Spread.Model.AggregationType.Sum);

  This works as expected. But now, I need to express this total as Hours so I need to divide by 60. How can I do that? I noticed there is a Custom aggregationtype but I can't find any example to use it.

  Can someone let me know how this can be done?

  Thank you.

• 

  reeva

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:24 am EST

  Hello,

  You can try setting the formula of the cell in trailing row as given below and in order to use AggregationType Custom you need to trap the Aggregate function of the DefautSheetDataModel and then set the formula there. Please have a look at the code:-
  
  

              fpSpread1.ActiveSheet.Rows.Count = 10;
  
              FarPoint.Win.Spread.CellType.NumberCellType num = new FarPoint.Win.Spread.CellType.NumberCellType();
  
              num.DecimalPlaces = 0;
  
              fpSpread1.Sheets[0].Columns[0].CellType = num;
  
              fpSpread1.ActiveSheet.FrozenTrailingRowCount = 1;
  
              int rowCount = fpSpread1.ActiveSheet.Rows.Count - 1;
  
              fpSpread1.Sheets[0].Cells[fpSpread1.ActiveSheet.Rows.Count - 1, 0].Formula = "Sum(A1:A" + rowCount + ")/60 ";

  Hope this will help you. Thanks.
  
  

• 

  kavita123

  • Post Options:
  • Link
    Copy

  Posted 8 September 2017, 5:24 am EST

  I think you should use SUMIF: (sum values between B2-B8, using value in A2-A8 as criterea, using the specified condition)

  =SUMIF(A2:A8,“=X”,B2:B8)

  =SUMIF(A2:A8,“=Y”,B2:B8) hope it will work for you.