Find All instances and sum them up VBA

[mysticmario]

Hi, I am using a code to find a cell value and then take the adjacent cell value to my report document: this is the code:

'2.ORGANZIACJA'
ActiveSheet.Columns("D:D").Select
Set Cell = Selection.Find(what:="ORGANIZACJA", after:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Cell Is Nothing Then
    Sheets(LastSheet).Range("S12").Value = 0
    Sheets(LastSheet).Range("T12").Value = 0
Else
    Sheets(LastSheet).Range("S12").Value = Cell.Offset(0, 1).Value
    Sheets(LastSheet).Range("T12").Value = Cell.Offset(0, 3).Value
End If
'------'

It works great but now I encountered a problem where i have multiple instances of this value in column. let's say the value is called "painting the element", I want to find all instances of this value in the column D, sum it up and "ship" it to Sheets(LastSheet).Range("R25")
Can't wrap my head around how to do it.
anyone can help?

 

[JamesCanale]

VBA seems overkill for this.

MrExcelPlayground5.xlsx
	D	E	F	G	H	I	J	K	L	M	N
1	Thing	Value1	Stuff?	Value3					Other Sheet somewhere
2	A	15		100					Looking for	Value 1	Value 3
3	B	20		103					D	105	245
4	C	25		106
5	D	30		109
6	E	35		112
7	F	40		115
8	G	45		118
9	H	50		121
10	I	55		124
11	J	60		127
12	K	65		130
13	C	70		133
14	D	75		136
15	E	80		139
16	F	85		142
17	G	90		145
Sheet11

Cell Formulas
Range	Formula
M3	M3	=SUMPRODUCT(E2:E17,--(D2:D17=L3))
N3	N3	=SUMPRODUCT(G2:G17,--(D2:D17=L3))

This works for cell values being equal. If you want to find something in the column D, but not necessarily the whole thing, that can be done too.

 

[mysticmario]

Even before your reply I thought of an idea where I force a =SUMIFS formula into one of the active sheet cells via vba and then import the result into another workbook as value.
Something like this
ActiveSheet.Range("X2").Formula= "=SUMIF(D:D;"Val";H:H)"
Sheets(LastSheet).Range("R25").Value =Range("X2").Value


Or sth similar