One To Many Relationship

[sav248]

I am looking for a single formula that will aggregate data for a parent group with a one to many relationship. Imagine I have sales by employee and I have an employee to group mapping table. My goal is to create a table with sales by group without mapping the group to the sales by employee table.

Sales By Employee

[TABLE="width: 200"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Jeff[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]30
[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

Employee to Group Map

[TABLE="width: 200"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Salesperson[/TD]
[/TR]
[TR]
[TD]Widgets[/TD]
[TD]Bob[/TD]
[/TR]
[TR]
[TD]Widgets[/TD]
[TD]Sarah[/TD]
[/TR]
[TR]
[TD]Widgets[/TD]
[TD]Jane[/TD]
[/TR]
[TR]
[TD]Clocks[/TD]
[TD]Jeff[/TD]
[/TR]
[TR]
[TD]Clocks[/TD]
[TD]Tom[/TD]
[/TR]
</tbody>[/TABLE]

Sales By Group

[TABLE="width: 100"]
<tbody>[TR]
[TD]Group[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Widgets[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Clocks[/TD]
[TD]50
[/TD]
[/TR]
</tbody>[/TABLE]

Is there a single formula that I could put into the Sales column of Sales By Group that would return this result without having to add a third column to the Sales by Employee table?

I need a lookup function that would return the array of employees based on group then sum sales based on this. Unfortunately it doesn't seem possible to nest an array function within a function.

I could do this with a UDF but my employer has an extreme aversion to VBA.

Any different approaches? Many thanks. This is my first post on an excel msg board so forgive me if it is unclear.

 

[Marcelo Branco]

Maybe...


[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]

Employee​

[/td][td]

Sales​

[/td][td] [/td][td]

Group​

[/td][td]

Salesperson​

[/td][td] [/td][td]

Group​

[/td][td]

Sales​

[/td][/tr]
[tr][td]

2​

[/td][td]

Bob​

[/td][td]

10​

[/td][td] [/td][td]

Widgets​

[/td][td]

Bob​

[/td][td] [/td][td]

Widgets​

[/td][td]

100​

[/td][/tr]
[tr][td]

3​

[/td][td]

Jeff​

[/td][td]

20​

[/td][td] [/td][td]

Widgets​

[/td][td]

Sarah​

[/td][td] [/td][td]

Clocks​

[/td][td]

50​

[/td][/tr]
[tr][td]

4​

[/td][td]

Tom​

[/td][td]

30​

[/td][td] [/td][td]

Widgets​

[/td][td]

Jane​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

5​

[/td][td]

Sarah​

[/td][td]

40​

[/td][td] [/td][td]

Clocks​

[/td][td]

Jeff​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

6​

[/td][td]

Jane​

[/td][td]

50​

[/td][td] [/td][td]

Clocks​

[/td][td]

Tom​

[/td][td] [/td][td] [/td][td] [/td][/tr]
[/table]

Formula in H2 copied down
=SUMPRODUCT(SUMIF($A$2:$A$6,$E$2:$E$6,$B$2:$B$6),--($D$2:$D$6=G2))

Hope this helps

M.

 

[sav248]

Thank you Marcelo. This is exactly what I needed.

 

[Marcelo Branco]

Thank you Marcelo. This is exactly what I needed.

You are welcome and thanks for the feedback.

M,

 

[sav248]

You are welcome and thanks for the feedback.

M,

Is there any way to extend this to multiple criterion? In cell L2 I would like all cash sales for the widget team, in L3 cash sales for the clock team.

Thank you!
[TABLE="class: grid, width: 796"]
<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]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee[/TD]
[TD]Sales[/TD]
[TD]Payment Type[/TD]
[TD][/TD]
[TD]Group[/TD]
[TD]Salesperson[/TD]
[TD][/TD]
[TD]Payment Type[/TD]
[TD]CashOrCredit[/TD]
[TD][/TD]
[TD]Group[/TD]
[TD]Cash Sales[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bob[/TD]
[TD]10[/TD]
[TD]Trade Credit[/TD]
[TD][/TD]
[TD]Widgets[/TD]
[TD]Bob[/TD]
[TD][/TD]
[TD]Trade Credit[/TD]
[TD]Credit[/TD]
[TD][/TD]
[TD]Widgets[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jeff[/TD]
[TD]20[/TD]
[TD]COD[/TD]
[TD][/TD]
[TD]Widgets[/TD]
[TD]Sarah[/TD]
[TD][/TD]
[TD]COD[/TD]
[TD]Cash[/TD]
[TD][/TD]
[TD]Clocks[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Tom[/TD]
[TD]30[/TD]
[TD]Cash[/TD]
[TD][/TD]
[TD]Widgets[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]Cash[/TD]
[TD]Cash[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Sarah[/TD]
[TD]40[/TD]
[TD]Credit Card[/TD]
[TD][/TD]
[TD]Clocks[/TD]
[TD]Jeff[/TD]
[TD][/TD]
[TD]Credit Card[/TD]
[TD]Credit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Jane[/TD]
[TD]50[/TD]
[TD]Past Due[/TD]
[TD][/TD]
[TD]Clocks[/TD]
[TD]Tom[/TD]
[TD][/TD]
[TD]Past Due[/TD]
[TD]Credit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Bob[/TD]
[TD]50[/TD]
[TD]COD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Jane[/TD]
[TD]50[/TD]
[TD]Credit Card[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Jeff[/TD]
[TD]20[/TD]
[TD]Cash[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try this in L2 (copy down to L3)

=SUMPRODUCT(--ISNUMBER(MATCH(K2&"|"&$A$2:$A$9,$E$2:$E$6&"|"&$F$2:$F$6,0)),--ISNUMBER(MATCH($C$2:$C$9&"|"&"Cash",$H$2:$H$6&"|"&$I$2:$I$6,0)),$B$2:$B$9)

M.