Linking values from another sheet to another and adding them together and getting rid of duplicate values

[psmi123]

There are 30 functions and each one is tied to revenue. So I know how much revenue is tied to each function.

[TABLE="width: 128"]
<tbody>[TR]
[TD]Functions[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]1A[/TD]
[TD="align: right"]$24.00[/TD]
[/TR]
[TR]
[TD]2A[/TD]
[TD="align: right"]$54.00[/TD]
[/TR]
[TR]
[TD]3A[/TD]
[TD="align: right"]$36.00[/TD]
[/TR]
[TR]
[TD]5A[/TD]
[TD="align: right"]$23.00[/TD]
[/TR]
[TR]
[TD]8A[/TD]
[TD="align: right"]$25.00[/TD]
[/TR]
[TR]
[TD]9A[/TD]
[TD="align: right"]$42.00[/TD]
[/TR]
[TR]
[TD]11A[/TD]
[TD="align: right"]$35.00[/TD]
[/TR]
[TR]
[TD]13A[/TD]
[TD="align: right"]$75.00[/TD]
[/TR]
[TR]
[TD]1B[/TD]
[TD="align: right"]$79.00[/TD]
[/TR]
[TR]
[TD]2B[/TD]
[TD="align: right"]$6.00[/TD]
[/TR]
[TR]
[TD]3B[/TD]
[TD="align: right"]$80.00[/TD]
[/TR]
[TR]
[TD]5B[/TD]
[TD="align: right"]$7.00[/TD]
[/TR]
[TR]
[TD]6B[/TD]
[TD="align: right"]$46.00[/TD]
[/TR]
[TR]
[TD]8B[/TD]
[TD="align: right"]$45.00[/TD]
[/TR]
[TR]
[TD]1C[/TD]
[TD="align: right"]$26.00[/TD]
[/TR]
[TR]
[TD]2C[/TD]
[TD="align: right"]$84.00[/TD]
[/TR]
[TR]
[TD]3C[/TD]
[TD="align: right"]$58.00[/TD]
[/TR]
[TR]
[TD]4C[/TD]
[TD="align: right"]$25.00[/TD]
[/TR]
[TR]
[TD]6C[/TD]
[TD="align: right"]$13.00[/TD]
[/TR]
[TR]
[TD]8C[/TD]
[TD="align: right"]$25.00[/TD]
[/TR]
[TR]
[TD]1D[/TD]
[TD="align: right"]$47.00[/TD]
[/TR]
[TR]
[TD]2D[/TD]
[TD="align: right"]$39.00[/TD]
[/TR]
[TR]
[TD]3D[/TD]
[TD="align: right"]$59.00[/TD]
[/TR]
[TR]
[TD]3E[/TD]
[TD="align: right"]$23.00[/TD]
[/TR]
[TR]
[TD]4E[/TD]
[TD="align: right"]$15.00[/TD]
[/TR]
[TR]
[TD]5E[/TD]
[TD="align: right"]$62.00[/TD]
[/TR]
[TR]
[TD]6E[/TD]
[TD="align: right"]$72.00[/TD]
[/TR]
[TR]
[TD]7E[/TD]
[TD="align: right"]$54.00[/TD]
[/TR]
[TR]
[TD]8E[/TD]
[TD="align: right"]$11.00[/TD]
[/TR]
</tbody>[/TABLE]

I want to find the revenue of each sector, the sectors are made of solutions and the solutions are made of the functions. So i need to find out an approach on how to count the revenue per sector but i dont want it counting revenue for the same function more than once because there are solutions that share the same function and i dont want them being counted twice.

[TABLE="width: 534"]
<tbody>[TR]
[TD]Sector[/TD]
[TD]Sector Solution[/TD]
[TD]Functions[/TD]
[TD]Functions[/TD]
[TD]Functions[/TD]
[TD]Revenue[/TD]
[/TR]
[TR]
[TD]Auto[/TD]
[TD]Sedan[/TD]
[TD]1A[/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Auto[/TD]
[TD]Crossover[/TD]
[TD]1A[/TD]
[TD]5A[/TD]
[TD]2A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Auto[/TD]
[TD]Bike[/TD]
[TD]2A[/TD]
[TD]8A[/TD]
[TD]9A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Auto[/TD]
[TD]Coupe[/TD]
[TD]3A[/TD]
[TD]11A[/TD]
[TD]2A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Auto[/TD]
[TD]Supercar[/TD]
[TD]13A[/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aero[/TD]
[TD]Jets[/TD]
[TD]1B[/TD]
[TD]2B[/TD]
[TD]3B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aero[/TD]
[TD]Boat Plane[/TD]
[TD]2B[/TD]
[TD]5B[/TD]
[TD]6B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aero[/TD]
[TD]Small Planes[/TD]
[TD]3B[/TD]
[TD]8B[/TD]
[TD]2B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Energy[/TD]
[TD]Wind[/TD]
[TD]1C[/TD]
[TD]2C[/TD]
[TD]3C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Energy[/TD]
[TD]Water[/TD]
[TD]4C[/TD]
[TD]1C[/TD]
[TD]6C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Energy[/TD]
[TD]Solar[/TD]
[TD]1C[/TD]
[TD]8C[/TD]
[TD]4C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Gas[/TD]
[TD]Mining[/TD]
[TD]1D[/TD]
[TD]2D[/TD]
[TD]3D[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Technologies[/TD]
[TD]Blockchain[/TD]
[TD]5E[/TD]
[TD]7E[/TD]
[TD]3E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Technologies[/TD]
[TD]IoT[/TD]
[TD]4E[/TD]
[TD]5E[/TD]
[TD]6E[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Technologies[/TD]
[TD]5G[/TD]
[TD]7E[/TD]
[TD]8E[/TD]
[TD]5E[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Functions[/TD]
[TD]Revenue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]1A[/TD]
[TD]

$24.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]2A[/TD]
[TD]

$54.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]3A[/TD]
[TD]

$36.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]5A[/TD]
[TD]

$23.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]8A[/TD]
[TD]

$25.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]9A[/TD]
[TD]

$42.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]11A[/TD]
[TD]

$35.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]13A[/TD]
[TD]

$75.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]1B[/TD]
[TD]

$79.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]2B[/TD]
[TD]

$6.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]3B[/TD]
[TD]

$80.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]5B[/TD]
[TD]

$7.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]6B[/TD]
[TD]

$46.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]8B[/TD]
[TD]

$45.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]1C[/TD]
[TD]

$26.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]2C[/TD]
[TD]

$84.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

18​

[/TD]
[TD]3C[/TD]
[TD]

$58.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

19​

[/TD]
[TD]4C[/TD]
[TD]

$25.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

20​

[/TD]
[TD]6C[/TD]
[TD]

$13.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

21​

[/TD]
[TD]8C[/TD]
[TD]

$25.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

22​

[/TD]
[TD]1D[/TD]
[TD]

$47.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

23​

[/TD]
[TD]2D[/TD]
[TD]

$39.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

24​

[/TD]
[TD]3D[/TD]
[TD]

$59.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

25​

[/TD]
[TD]3E[/TD]
[TD]

$23.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

26​

[/TD]
[TD]4E[/TD]
[TD]

$15.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

27​

[/TD]
[TD]5E[/TD]
[TD]

$62.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

28​

[/TD]
[TD]6E[/TD]
[TD]

$72.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

29​

[/TD]
[TD]7E[/TD]
[TD]

$54.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

30​

[/TD]
[TD]8E[/TD]
[TD]

$11.00​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

31​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

32​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

33​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

34​

[/TD]
[TD]Sector[/TD]
[TD]Sector Solution[/TD]
[TD]Functions[/TD]
[TD]Functions[/TD]
[TD]Functions[/TD]
[TD]Revenue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

35​

[/TD]
[TD]Auto[/TD]
[TD]Sedan[/TD]
[TD]1A[/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD]

114​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

36​

[/TD]
[TD]Auto[/TD]
[TD]Crossover[/TD]
[TD]1A[/TD]
[TD]5A[/TD]
[TD]2A[/TD]
[TD]

101​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

37​

[/TD]
[TD]Auto[/TD]
[TD]Bike[/TD]
[TD]2A[/TD]
[TD]8A[/TD]
[TD]9A[/TD]
[TD]

121​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

38​

[/TD]
[TD]Auto[/TD]
[TD]Coupe[/TD]
[TD]3A[/TD]
[TD]11A[/TD]
[TD]2A[/TD]
[TD]

125​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

39​

[/TD]
[TD]Auto[/TD]
[TD]Supercar[/TD]
[TD]13A[/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD]

165​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

40​

[/TD]
[TD]Aero[/TD]
[TD]Jets[/TD]
[TD]1B[/TD]
[TD]2B[/TD]
[TD]3B[/TD]
[TD]

165​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

41​

[/TD]
[TD]Aero[/TD]
[TD]Boat Plane[/TD]
[TD]2B[/TD]
[TD]5B[/TD]
[TD]6B[/TD]
[TD]

59​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

42​

[/TD]
[TD]Aero[/TD]
[TD]Small Planes[/TD]
[TD]3B[/TD]
[TD]8B[/TD]
[TD]2B[/TD]
[TD]

131​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

43​

[/TD]
[TD]Energy[/TD]
[TD]Wind[/TD]
[TD]1C[/TD]
[TD]2C[/TD]
[TD]3C[/TD]
[TD]

168​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

44​

[/TD]
[TD]Energy[/TD]
[TD]Water[/TD]
[TD]4C[/TD]
[TD]1C[/TD]
[TD]6C[/TD]
[TD]

64​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

45​

[/TD]
[TD]Energy[/TD]
[TD]Solar[/TD]
[TD]1C[/TD]
[TD]8C[/TD]
[TD]4C[/TD]
[TD]

76​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

46​

[/TD]
[TD]Gas[/TD]
[TD]Mining[/TD]
[TD]1D[/TD]
[TD]2D[/TD]
[TD]3D[/TD]
[TD]

145​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

47​

[/TD]
[TD]Technologies[/TD]
[TD]Blockchain[/TD]
[TD]5E[/TD]
[TD]7E[/TD]
[TD]3E[/TD]
[TD]

139​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

48​

[/TD]
[TD]Technologies[/TD]
[TD]IoT[/TD]
[TD]4E[/TD]
[TD]5E[/TD]
[TD]6E[/TD]
[TD]

149​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

49​

[/TD]
[TD]Technologies[/TD]
[TD]5G[/TD]
[TD]7E[/TD]
[TD]8E[/TD]
[TD]5E[/TD]
[TD]

127​

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



F35=SUMPRODUCT(SUMIF($A$2:$A$30,C35:E35,$B$2:$B$30)) copy down

 

[psmi123]

how would i format it if i dont want it counting duplicates? For example every sector uses function 2A, meaning the revenue from 2A should be split to each sector. I dont want the revenue being counted more than once. Is there a way to do that?

 

[MickG]

With your first Table in columns "A & B" and the second Table in columns "D to I", then try this for results in column "I".

[COLOR="Navy"]Sub[/COLOR] MG11Jul28
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Tot [COLOR="Navy"]As[/COLOR] Double, ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR="Navy"]Set[/COLOR] dic = CreateObject("scripting.dictionary")
    dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 1): [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("F2", Range("F" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] ac = 0 To 2
        dic(Dn.Offset(, ac).Value) = dic(Dn.Offset(, ac).Value) + 1
    [COLOR="Navy"]Next[/COLOR] ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] ac = 0 To 2
        [COLOR="Navy"]If[/COLOR] .exists(Dn.Offset(, ac).Value) [COLOR="Navy"]Then[/COLOR]
            Tot = Tot + .Item(Dn.Offset(, ac).Value) / dic(Dn.Offset(, ac).Value)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] ac
        Dn.Offset(, 3).Value = Format(Tot, "$0.00"): Tot = 0
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[psmi123]

Thank you so much! So where are the variables I can change if I need to use a bigger table. Also is there a way to do this on a different page?

 

[MickG]

Try this:-
NB:- The First table is in sheet1 Columns "A & B".
The second Table is in sheet2 starting "A1"
The code should take care of the number of columns and rows.
NB:- The "Revenue" Column in sheet2 (Last Column) must have the Word "Revenue" as the header.

[COLOR=navy]Sub[/COLOR] MG11Jul59
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Tot [COLOR=navy]As[/COLOR] Double, Ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] dic [COLOR=navy]As[/COLOR] Object

'[COLOR=green][B]First table "Sheet1" columns "A & B".[/B][/COLOR]
[COLOR=navy]With[/COLOR] Sheets("Sheet1")
    [COLOR=navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With
[COLOR=navy]
With[/COLOR] CreateObject("scripting.dictionary")
    .CompareMode = vbTextCompare
[COLOR=navy]Set[/COLOR] dic = CreateObject("scripting.dictionary")
    dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng: .Item(Dn.Value) = Dn.Offset(, 1): [COLOR=navy]Next[/COLOR]

'[COLOR=green][B]Second table on sheet 2[/B][/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Sheets("Sheet2").Range("A1").CurrentRegion
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    dic(Dn.Offset(, Ac).Value) = dic(Dn.Offset(, Ac).Value) + 1
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]
For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Columns(3).Cells
   [COLOR=navy]If[/COLOR] Not Dn.Row = 1 [COLOR=navy]Then[/COLOR]
    [COLOR=navy]For[/COLOR] Ac = 0 To Rng.Columns.Count - 1
        [COLOR=navy]If[/COLOR] .exists(Dn.Offset(, Ac).Value) [COLOR=navy]Then[/COLOR]
            Tot = Tot + .Item(Dn.Offset(, Ac).Value) / dic(Dn.Offset(, Ac).Value)
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] Ac
        Dn.Offset(, Rng.Columns.Count - 3).Value = Format(Tot, "$0.00"): Tot = 0
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick