SUMPRODUCT with MIN and MAX?

[iggydarsa]

Hi All,

I have a table on Sheet1 that looks like this:


Company --- User --- Count --- %
CompA --- UserA --- 100 --- 0
CompB --- UserB --- 50 --- 25
CompC --- UserA --- 75 --- 10
CompD --- UserC --- 50 --- 20
CompE --- UserB --- 25 --- 25
CompF --- UserC --- 50 --- 10
CompG --- UserB --- 10 --- 50



On Sheet2 I have this and need a formula to get the value from Sheet1:

User --- Company with lowest %
UserA --- [Need formula here]
UserB --- [Need formula here]
UserC --- [Need formula here]


The rule is find the Company that User works for with the lowest percantage. If there are multiple records with the lowest % then get the one with the highest count.

So the outcome should look like this:

User --- Company with lowest %
UserA --- CompA
UserB --- CompB (Company B and E has the lowest % but B has the highest count)
UserC --- CompF

I know SUMPRODUCT returns number so it might not work to get the company name.

PS: for this thing I'm working on Pivot tables wont help.

Any help is greatly appreciated.

 

[DanteAmor]

Try this array formula

Book1
	A	B	C	D
1	Company	User	Count	%
2	CompA	UserA	100	0
3	CompB	UserB	50	25
4	CompC	UserA	75	10
5	CompD	UserC	50	20
6	CompE	UserB	25	25
7	CompF	UserC	50	10
8	CompG	UserB	100	50
Sheet1

Book1
	A	B
1	User	lowest %
2	UserA	CompA
3	UserB	CompB
4	UserC	CompF
Sheet2

Cell Formulas
Range	Formula
B2	{=INDEX(Sheet1!$A$1:$A$8,MAX((Sheet1!$B$2:$B$8=A2)*(Sheet1!$C$2:$C$8=MAX(IF(Sheet1!$B$2:$B$8=A2,IF(Sheet1!$D$2:$D$8=MIN(IF(Sheet1!$B$2:$B$8=A2,Sheet1!$D$2:$D$8)),Sheet1!$C$2:$C$8))))*(Sheet1!$D$2:$D$8=MIN(IF(Sheet1!$B$2:$B$8=A2,Sheet1!$D$2:$D$8)))*(ROW(Sheet1!$A$2:$A$8))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

This is a case where using a helper column can really simplify things. Consider:

Book1
	A	B	C	D	E	F	G	H
1	Company	User	Count	Percent	Rank		User	Company w/ lowest %
2	CompA	UserA	100	0	1		UserA	CompA
3	CompB	UserB	50	25	1		UserB	CompB
4	CompC	UserA	75	10	2		UserC	CompF
5	CompD	UserC	50	20	2
6	CompE	UserB	25	25	2
7	CompF	UserC	50	10	1
8	CompG	UserB	10	50	3
Sheet3

Cell Formulas
Range	Formula
H2	=INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$8)/($B$2:$B$8=G2)/($E$2:$E$8=1),1))
E2	=COUNTIFS(B:B,B2,D:D,"<"&D2)+COUNTIFS(B:B,B2,D:D,D2,C:C,">"&C2)+1

 

[iggydarsa]

WOW! Thanks!

 

[DanteAmor]

You're welcome!