Sumifs Sales Report

[lizomgwtf]

Hello,

I am trying to provide commissions statements to my sales people. My sales report lists all levels for each account: Salesperson, Sales Manager, and Director. In some cases, the employee ID in all three columns is the same person (the department director manages a few highly important accounts.) I am trying to sum the total sales for each person without double or triple counting any rows. I'm having trouble returning the sales for all accounts of a director using sumifs. In this example, Employee 1 and 2 exist in multiple columns, but I only want to count each account once. Each period, there are multiple changes and managers and directors fill in at different levels based on leaves, personnel changes, etc. I also don't want to ever miss some sales when an employee gets a promotion. How can I sumifs the sales column if the Employee's ID exists in ANY of the 3 columns?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Director[/TD]
[TD]Manager[/TD]
[TD]Salesperson[/TD]
[TD]Account[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Employee 1[/TD]
[TD]Employee 1[/TD]
[TD]ABC Company[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Employee 1[/TD]
[TD]Employee 2[/TD]
[TD]DEF Company[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Employee 2[/TD]
[TD]Employee 2[/TD]
[TD]GHI Company[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]Employee 1[/TD]
[TD]Employee 2[/TD]
[TD]Employee 3[/TD]
[TD]XYZ Company[/TD]
[TD]900[/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi,

Is this the output you need?

Book1
	A	B	C	D	E
1	Director	Manager	Salesperson	Account	Sales
2	Employee 1	Employee 1	Employee 1	ABC Company	$500.00
3	Employee 1	Employee 1	Employee 2	DEF Company	$600.00
4	Employee 1	Employee 2	Employee 2	GHI Company	$200.00
5	Employee 1	Employee 2	Employee 3	XYZ Company	$900.00
6
7		Director	Manager	Salesperson
8	Employee 1	2200	1100	$500.00
9	Employee 2	0	1100	$800.00
10	Employee 3	0	0	$900.00
Sheet1

Cell Formulas
Range	Formula
B8	=SUMPRODUCT((A$1&A$2:A$5=B$7&$A8)*$E$2:$E$5)

 

[lizomgwtf]

Not really. Essentially, the total that Employee 1 is due commissions on is 2200. As I have it right now, my formulas are trying to pay him on 3800 worth of sales. This report is huge and varies in size and order every month. I need a formula to count each account he is owner of (in any capacity) only once and return his total eligible sales in a single sell on my calculation sheet.