iLogic - Excel sum particular ranges

[bespel]

Hi!

This is my problem.

I have and Excel named myfile.xlsx

that is just like this, in the sheet "MYSHEET1"

I would like with iLogic to find the rows with the same materials and sum their volumes and write it in a desired cell.

For example:

Find all the row for the items that are made with STEEL, sum their Volumes and write in the cell I2

Find all the row for the items that are made with GOLD, sum their Volumes and write in the cell J2

And obtain this:

I can find the first row with steel, but i am already stopped with the code..

Do you have any ideas?

This is really difficult for me and I suspect..that can be difficult in general..

Thank you!

[Sergio.D.Suárez]

Hi, this could be a way.
You must specify the path of the excel file.
The ilogic rule will first define the total values in zero, then go row by row until finding the ones corresponding to the "STEEL" or the "GOLD" until finally adding them in the corresponding cell.
Surely there will be alternative ways to get what you want, this was the first that occurred to me.

Sub Main() 
'On Error Resume Next 
Dim Dirxlsx As String = "E:\myfile.xlsx"      ''' Specify the template path
Dim osheet As String = "MYSHEET1"             ''' Specify the sheet of the template

xlApp = CreateObject("Excel.Application")

xlApp.Visible = True
xlWorkbook = xlApp.Workbooks.Open(Dirxlsx)
xlWorksheet = xlWorkbook.Worksheets.Item(osheet)

Dim TotSteel As Double = 0
Dim TotGold As Double = 0

Dim row As Integer = 2  ''' Indicate from which row the read of excel values will begin

Line1 :

oItem = GoExcel.CellValue(Dirxlsx, osheet, "A" & row)
oMaterial = GoExcel.CellValue(Dirxlsx, osheet, "B" & row)
oVolume = GoExcel.CellValue(Dirxlsx, osheet, "D" & row)
If IsNumeric(oItem) Then
	
	If oMaterial = "STEEL" Then
	TotSteel= TotSteel + oVolume
	End If
	If oMaterial = "GOLD" Then
	TotGold= TotGold + oVolume
	End If
	
	row = row + 1
	GoTo Line1

End If
xlWorksheet.Range("I2").Value = TotSteel
xlWorksheet.Range("J2").Value = TotGold

xlWorksheet.Columns("A:Z").AutoFit
xlWorkbook.Save ' Save Document
xlApp.Quit 'Quit Application
End Sub

 I hope it is useful for you