16,003,333 members
See more:
My code works as SUMIFS function does.
Below code just completes this below formula that is.
`=SUMIFS(Inventory!\$C\$2:\$C\$500000,Inventory!\$B\$2:\$B\$500000,'Current Stock'!B2,Inventory!\$A\$2:\$A\$500000,'Current Stock'!A2,Inventory!\$G\$2:\$G\$500000,"="&'Current Stock'!G2)`

How to minus this below formula from above one.
`-SUMIFS(Sold!\$C\$2:\$C\$500000,Sold!\$B\$2:\$B\$500000,'Current Stock'!B2,Sold!\$A\$2:\$A\$500000,'Current Stock'!A2,Sold!\$G\$2:\$G\$500000,"="&'Current Stock'!G2)`

The complete formula will be as.
`=SUMIFS(Inventory!\$C\$2:\$C\$500000,Inventory!\$B\$2:\$B\$500000,'Current Stock'!B2,Inventory!\$A\$2:\$A\$500000,'Current Stock'!A2,Inventory!\$G\$2:\$G\$500000,"="&'Current Stock'!G2)-SUMIFS(Sold!\$C\$2:\$C\$500000,Sold!\$B\$2:\$B\$500000,'Current Stock'!B2,Sold!\$A\$2:\$A\$500000,'Current Stock'!A2,Sold!\$G\$2:\$G\$500000,"="&'Current Stock'!G2)`

Any solution will be greatly appreciated. If there would be an another solution using array then please share.

Code
```Dim dict As Object
Dim searchrange As Range

With Sheet1
Dim last_y As Long
Dim i As Long
Set dict = CreateObject("Scripting.Dictionary")
last_y = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To last_y

dict(.Cells(i, 1).Value & .Cells(i, 2).Value & .Cells(i, 7).Value) = dict(.Cells(i, 1).Value & .Cells(i, 2).Value & .Cells(i, 7).Value) + .Cells(i, 3).Value

Next i
End With

With Sheet3
last_y = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = 2 To last_y
.Cells(i, 3).Value = dict(.Cells(i, 1).Value & .Cells(i, 2).Value & .Cells(i, 7).Value)
Next i
End With```

What I have tried:

I have been finding an way online but could not find any thing.
Posted
Maciej Los 11-Feb-21 13:22pm
Sorry, but i don't get you... Do you want to achieve that by using VBA code or via formulas?
The Mentee 12-Feb-21 5:42am
via VBA code