How to Calculate Two Columns in DataGridView

[plot1]

In my previous tutorial using DataGridView Control I explained on “How to Differentiate Two Cell Values in DataGridView Control”. This time I will teach you on how to calculate two columns in DataGridView Control.

This tutorial is very useful if you want to make a total of the two columns. For example a total of “Qty” and “Sales Price” Column.

Additionally, we will make a total of the “Amount” column.

To achieve this all you have to do is trigger the CellEndEdit Events.

1. Private
   
   Sub
   
   InvoiceDetailsDataGridView_CellEndEdit(
   
   ByVal
   
   sender As
   
   Object
   
   , ByVal
   
   e As
   
   System.
   
   Windows
   
   .
   
   Forms
   
   .
   
   DataGridViewCellEventArgs
   
   )
   
   Handles
   
   InvoiceDetailsDataGridView.
   
   CellEndEdit
   
2. Dim
   
   InvoiceDetails As
   
   DataGridView =
   
   DirectCast
   
   (
   
   sender, DataGridView)
   
3. 
   
4. If
   
   IsDBNull(
   
   InvoiceDetails(
   
   0
   
   , e.
   
   RowIndex
   
   )
   
   .
   
   Value
   
   )
   
   Then
   
   Exit
   
   Sub
   
5. 
   
6. InvoiceDetails(
   
   "Amount"
   
   , e.
   
   RowIndex
   
   )
   
   .
   
   Value
   
   =
   
   InvoiceDetails(
   
   "Qty"
   
   , e.
   
   RowIndex
   
   )
   
   .
   
   Value
   
   *
   
   InvoiceDetails(
   
   "SalesPrice"
   
   , e.
   
   RowIndex
   
   )
   
   .
   
   Value
   
7. 
   
8. If
   
   (
   
   e.
   
   ColumnIndex
   
   =
   
   2
   
   Or
   
   e.
   
   ColumnIndex
   
   =
   
   4
   
   )
   
   And
   
   InvoiceDetails.
   
   Rows
   
   .
   
   Count
   
   >
   
   0
   
   Then
   
9. TotalTextBox.
   
   Text
   
   =
   
   Total(
   
   )
   
   .
   
   ToString
   
10. End
    
    If
    
11. End
    
    Sub
    

This assume that the name of your DataGridView Control is InvoiceDetailsDataGridView and you have change the name of the column to “Qty”, “SalesPrice”, and “Amount”.

[inline:datagridview_columns_properties.jpg=DataGridView Column Properties]

e.

ColumnIndex

=

2

Or

e.

ColumnIndex

=

4

assumes that “Qty” column has an index equals to 2 and “Amount” equals to 4.

Now here’s the Total function.

1. Private
   
   Function
   
   Total(
   
   )
   
   As
   
   Double
   
2. Dim
   
   tot As
   
   Double
   
   =
   
   0
   
3. Dim
   
   i As
   
   Integer
   
   =
   
   0
   
4. 
   
5. For
   
   i =
   
   0
   
   To
   
   InvoiceDetailsDataGridView.
   
   Rows
   
   .
   
   Count
   
   -
   
   1
   
6. tot =
   
   tot +
   
   Convert.
   
   ToDouble
   
   (
   
   InvoiceDetailsDataGridView.
   
   Rows
   
   (
   
   i)
   
   .
   
   Cells
   
   (
   
   "Amount"
   
   )
   
   .
   
   Value
   
   )
   
7. Next
   
   i
   
8. 
   
9. Return
   
   tot
   
10. End
    
    Function
    

What you need here is a DataGridView Control and a TextBox named “TotalTextBox”.