title: How PowerShell Sum an Excel column and print the result?
body: <p>I have an excel file with 10 columns. I want to get the sum of the column with header "Sales" and print it on the console.
  How this can be done with PowerShell? I am using the below code but I do not know how to replace H with $i in the following expression:</p>
  
  <pre><code>='=SUM(H1:H'+$RowCount')'
  </code></pre>
  
  <p>Where H is column "Sales"</p>
  
  <pre><code>$Excel = New-Object -ComObject Excel.Application
  $Excel.Visible = $False
  $NewWorkbook = $Excel.Workbooks.open("C:\Test.xlsx")
  $NewWorksheet = $NewWorkbook.Worksheets.Item(1)
  $NewWorksheet.Activate() | Out-Null
  $NewWorksheetRange = $NewWorksheet.UsedRange
  $RowCount = $NewWorksheetRange.Rows.Count
  $ColumnCount = $NewWorksheetRange.Columns.Count
  for ($i = 1; $i -lt $ColumnCount; $i++)
  {
   if ($NewWorksheet.cells.Item(1,$i).Value2 -eq "Sales") 
     {
       $NewWorksheet.Cells.Item($RowCount+2,$i)='=SUM(H1:H'+$RowCount')'
       Write-Host $NewWorksheet.Cells.Item($RowCount+1,$i).Value2
     }
   } 
  $Excel.Application.DisplayAlerts=$False
  $NewWorkbook.SaveAs("C:\Test_New.xlsx")
  $NewWorkbook.close($false)
  $Excel.quit()
  spps -n excel
  </code></pre>
  
  <p>I have replaced:</p>
  
  <pre><code>$NewWorksheet.Cells.Item($RowCount+2,$i) ='=SUM(H1:H'+(1+$RowCount)+')'
  Write-Host $NewWorksheet.Cells.Item($RowCount+2,$i).Value2
  </code></pre>
  
  <p>with:</p>
  
  <pre><code>$FirstCell = $NewWorksheet.Cells(2,$i).Address('+True, False+')
  $LastCell = $NewWorksheet.Cells(1+$RowCount,$i).Address('+True, False+')
  $NewWorksheet.Cells.Item($RowCount+2,$i)='=SUM('+$FirstCell+':'+$LastCell+')'
  Write-Host $NewWorksheet.Cells.Item($RowCount+2,$i).Value2
  </code></pre>