Unique Count with criteria under a distriubtion matrix

[silentbuddha]

Hi All,

I'm trying to figure out how to solve this Unique Count situation based on multiple criteria

Scenario: we have agents from different departments who produce different sales on different plan rates

Raw Data
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Employee[/TD]
[TD]Plan Rate[/TD]
[TD]Payout pts[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]20[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]30[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]30[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]


Matrix

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Matrix
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Plan Rates
[/TD]
[TD="align: center"]Plan Rates
[/TD]
[TD="align: center"]Plan Rates
[/TD]
[TD="align: center"]Plan Rates
[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"] Sales Volume bucket
[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]count of unique # of employees who made one sale on a plan_rate of 10.[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"] Sales Volume bucket
[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]count of unique # of employees who made two sales on a plan_rate of 20.[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"] Sales Volume bucket
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]count of unique # of employees who made three sales on a plan_rate of 30.[/TD]
[/TR]
</tbody>[/TABLE]


Thanks

 

[Aladin Akyurek]

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]rate 0
[/TD]
[TD]rate 10
[/TD]
[TD]rate 20
[/TD]
[TD]rate 30
[/TD]
[/TR]
[TR]
[TD]1 sales
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp_2
--------
total count of employees = 1
[/TD]
[/TR]
[TR]
[TD]2 sales
[/TD]
[TD][/TD]
[TD]emp_1
--------
total count of employees = 1
[/TD]
[TD]emp_1
--------
total count of employees = 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3 sales
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]emp_3
--------
total count of employees = 1
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Hi Aladin,

my aplogies, the above table shold be the output I am hoping to achieve...thanks

How do you obtain 1 for 1 sales x rate 30?

 

[silentbuddha]

Hi Alan,

it's okay...i found a solution...thanks

 

[Aladin Akyurek]

Hi Alan,

it's okay...i found a solution...thanks

That's great, but we have still no idea around here what the solution is and the problem it tackles with...

 

[silentbuddha]

Hi Alain,

hypothetical Question would be ..for example...how many employees had a a total of 3 sales and whereby one of those sales was a 10$ rate card...

i solved it with a few sumif formulas...thanks for all your insight

 

[Aladin Akyurek]

Hi Alain,

hypothetical Question would be ..for example...how many employees had a a total of 3 sales and whereby one of those sales was a 10$ rate card...

i solved it with a few sumif formulas...thanks for all your insight

You still evade the question. Sumif formulas have nothing to do with "unique count" your topic title mentions. But, never mind and thanks for the feedback.

 

[silentbuddha]

Hi Aladin,

I was wonderig if you had any thought on the thread below? Thanks

http://www.mrexcel.com/forum/excel-...ota-distribution-factoring-%-phone-hours.html