Sum of unique values for conditional lookup

[drsgizmo]

For this dataset, I'm trying to write a formula in Table3 that will sum the revenue amount (from Table2) of any ID that is associated with domain in Table1. The formula should first pull the list of unique IDs that are associated with the domain, so for 'a.com' it would create the array {1,2,3}. It would then find the revenue amount for each of those IDs, so {1,2,3} becomes {100,200,400}. It would then sum that array, in this case to arrive at $700. I've tried so far this

{=SUM(IF(COUNTIFS(Table1[Domains],$G$3,Table1[ID],$D$3:$D$6)>0,INDEX(Table2[Revenue], MATCH($D$3:$D$6, Table2[ID],0)),0))}

and

{=SUM(IF($B$3:$B$18=G3,VLOOKUP($A$3:$A$18,$D$3:$E$6,2,0),0))}

neither of which work. Any help is very appreciated.

 

[Marcelo Branco]

Try

=SUM(SUMIF(Table2[ID],IF(FREQUENCY(IF(ISNUMBER(SEARCH([@Domains],Table1[Domain])),Table1[ID]),Table1[ID]),Table1[ID]),Table2[Revenue]))
Ctrl+Shift+Enter

M.

 

[drsgizmo]

works great, thanks very much!