Find items that total to zero within a subset

[theraindew]

I have a large data set that i have to work on every day and find out items that match to a zero.
Below is a sample data. Column A has the reference numbers. I have removed the suffixes and kept only the numbers in column B. Column C has amounts. Now, some of the amounts in column C will total to zero for the same reference numbers in column B. I want to identify these matching items and mark them in column D. I have given Yes and No as sample, but this could be anything. Is there a formula or even VBA that can achieve this? I tried sum/if/sumproduct but couldn't get it to work!
Appreciate any help on this.

Reference	Ref	Amount	Match
44004345450A	44004345450	-54,999	Yes
44004345450B	44004345450	-99,999	No
44004345450-A	44004345450	-79,999	No
44004345450C	44004345450	-20,799	No
44004345450-B	44004345450	-216,750	No
44004345450D	44004345450	30,500	Yes
44004345450E	44004345450	24,499	Yes
44004541765A	44004541765	-11,041	Yes
44004541765-A	44004541765	-35,998	No
44004541765-B	44004541765	-36,449	No
44004541765B	44004541765	11,041	Yes
44004740328A	44004740328	-38,998	Yes
44004740328-A	44004740328	-19,499	No
44004740328B	44004740328	38,998	Yes

 

[Dave Patton]

T202404.xlsm
	A	B	C	D
1
2	Reference	Ref	Amount	Match
3	44004345450A	44004345450	-54999	-54999
4	44004345450B	44004345450	-99999
5	44004345450-A	44004345450	79999
6	44004345450C	44004345450	-20799
7	44004345450-B	44004345450	216750
8	44004345450D	44004345450	30500
9	44004345450E	44004345450	24499
10	44004541765A	44004541765	-11041	-11041
11	44004541765-A	44004541765	35998
12	44004541765-B	44004541765	36449
13	44004541765B	44004541765	11041
14	44004740328A	44004740328	-38998	-38998
15	44004740328-A	44004740328	-19499
16	44004740328B	44004740328	38998
17
3e

Cell Formulas
Range	Formula
D3:D16	D3	=IF(B3<>B2,SUMIFS($C$3:$C$16,$A$3:$A$16,A3),"")

 

[Dave Patton]

T202404.xlsm
	A	B	C	D	E
1
2	Reference	Ref	Amount		Match
3	44004345450A	44004345450	-54999	1	0
4	44004345450B	44004345450	-99999		175951
5	44004345450-A	44004345450	79999		175951
6	44004345450C	44004345450	-20799		175951
7	44004345450-B	44004345450	216750		175951
8	44004345450D	44004345450	30500	1	0
9	44004345450E	44004345450	24499	1	0
10	44004541765A	44004541765	-11041	1	0
11	44004541765-A	44004541765	35998		72447
12	44004541765-B	44004541765	36449		72447
13	44004541765B	44004541765	11041	1	0
14	44004740328A	44004740328	-38998	1	0
15	44004740328-A	44004740328	-19499		-19499
16	44004740328B	44004740328	38998	1	0
17
3e

Cell Formulas
Range	Formula
E3:E16	E3	=IF(D3<>1,SUMPRODUCT(--($B$3:$B$16=B3),--($D$3:$D$16=0),$C$3:$C$16),SUMPRODUCT(--($B$3:$B$16=B3),--($D$3:$D$16=1),$C$3:$C$16))

 

[theraindew]

T202404.xlsm
	A	B	C	D	E
1
2	Reference	Ref	Amount		Match
3	44004345450A	44004345450	-54999	1	0
4	44004345450B	44004345450	-99999		175951
5	44004345450-A	44004345450	79999		175951
6	44004345450C	44004345450	-20799		175951
7	44004345450-B	44004345450	216750		175951
8	44004345450D	44004345450	30500	1	0
9	44004345450E	44004345450	24499	1	0
10	44004541765A	44004541765	-11041	1	0
11	44004541765-A	44004541765	35998		72447
12	44004541765-B	44004541765	36449		72447
13	44004541765B	44004541765	11041	1	0
14	44004740328A	44004740328	-38998	1	0
15	44004740328-A	44004740328	-19499		-19499
16	44004740328B	44004740328	38998	1	0
17
3e

Cell Formulas
Range	Formula
E3:E16	E3	=IF(D3<>1,SUMPRODUCT(--($B$3:$B$16=B3),--($D$3:$D$16=0),$C$3:$C$16),SUMPRODUCT(--($B$3:$B$16=B3),--($D$3:$D$16=1),$C$3:$C$16))

Hi Dave,
thank you for the response. How does column D show 1 for the matching items?

 

[Dave Patton]

Initially try putting the 1 in manually to see if this idea helps.
The matching may involve more than 2 numbers.