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

Employee	Sales
Bob	10
Jeff	20
Tom	30
Sarah	40
Jane	50

<tbody>
</tbody>

Employee to Group Map

Group	Salesperson
Widgets	Bob
Widgets	Sarah
Widgets	Jane
Clocks	Jeff
Clocks	Tom

<tbody>
</tbody>

Sales By Group

Group	Sales
Widgets	100
Clocks	50

<tbody>
</tbody>

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...

	A​	B​	C​	D​	E​	F​	G​	H​
1​	Employee​	Sales​		Group​	Salesperson​		Group​	Sales​
2​	Bob​	10​		Widgets​	Bob​		Widgets​	100​
3​	Jeff​	20​		Widgets​	Sarah​		Clocks​	50​
4​	Tom​	30​		Widgets​	Jane​
5​	Sarah​	40​		Clocks​	Jeff​
6​	Jane​	50​		Clocks​	Tom​

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!

A

B

C

D

E

F

G

H

I

J

K

L

1

Employee

Sales

Payment Type

Group

Salesperson

Payment Type

CashOrCredit

Group

Cash Sales

2

Bob

10

Trade Credit

Widgets

Bob

Trade Credit

Credit

Widgets

3

Jeff

20

COD

Widgets

Sarah

COD

Cash

Clocks

4

Tom

30

Cash

Widgets

Jane

Cash

Cash

5

Sarah

40

Credit Card

Clocks

Jeff

Credit Card

Credit

6

Jane

50

Past Due

Clocks

Tom

Past Due

Credit

7

Bob

50

COD

8

Jane

50

Credit Card

9

Jeff

20

Cash

<tbody>
</tbody>

 

[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.