Difference of Column with Multiples of the Same Entry

[raynebowx]

I want it to take the difference of one 1 column minus a certain box and add together only 1 unique value since theres multiples of the same entry.

I had

SUM(E8:E50)-L4

Which worked until column E had several entries with the same value. Then it added column E together so the number was massive.

TIA!

 

[Yongle]

You could uses a helper column

Formula in L6
=SUM(F8:F50)-L4

Formul in F8 copied down
=(COUNTIF($E$8:E8,E8)=1)*E8

Excel 2016 (Windows) 32 bit

	E​	F​	G​	H​	I​	J​	K​	L​	M​
3​
4​								10​
5​
6​								45​	=SUM(F8:F50)-L4
7​		HELPER
8​	1​	1​							=(COUNTIF($E$8:E8,E8)=1)*E8
9​	2​	2​
10​	3​	3​
11​	4​	4​
12​	5​	5​
13​	6​	6​
14​	7​	7​
15​	8​	8​
16​	9​	9​
17​	10​	10​
18​	1​	0​
19​	2​	0​
20​	3​	0​
21​	4​	0​
22​	5​	0​
23​	6​	0​
24​	7​	0​
25​	8​	0​
26​	9​	0​
27​	10​	0​
28​	1​	0​
29​	2​	0​
30​	3​	0​
31​	4​	0​
32​	5​	0​
33​	6​	0​
34​	7​	0​
35​	8​	0​
36​	9​	0​
37​	10​	0​
38​	1​	0​
39​	2​	0​
40​	3​	0​
41​	4​	0​
42​	5​	0​
43​	6​	0​
44​	7​	0​
45​	8​	0​
46​	9​	0​
47​	10​	0​
48​	1​	0​
49​	2​	0​
50​	3​	0​

Sheet: Sheet1