Ranking with sumproduct & countif with extra conditions

[azdraon]

Hey everyone. I have been stuck on this issue for a few days now. I have multiple criteria for the ranking, "State" and "Type". And I want it to be ranked based on the "Share". This is just a sample and actually have hundred of rows. I have figured it out so that it filters to the State and Type for the SUMPRODUCT(). But when I try to throw in a COUNTIF() I get crazy decimal places (see Group Rank (calc)).

Here is my formula. My desired result would be a rank with repeating values. Actually the decimals go out much further, and I can't symply "ROUND()" that I can figure out to resolve this issue.

Group Rank (calc) =SUMPRODUCT((--([@State]=[State])),(--([@[Line Of Business]]=[Line Of Business])),(--([@[Share]]<=[Share]))/COUNTIF([Share],[Share]))

State	Line of Business	Share	Group Rank (calc)
IA	Homeowners	2303​	1.3​
IA	Homeowners	257​	7.1​
IA	Homeowners	264​	6.1​
IA	Homeowners	264​	6.1​
IA	Homeowners	264​	6.1​
IA	Homeowners	335​	5.2​
IA	Homeowners	335​	5.2​
IA	Homeowners	355​	4.6​
IA	Homeowners	355​	4.6​
IA	Homeowners	500​	4.3​
IA	Homeowners	899​	3.3​
IA	Homeowners	899​	3.3​
IA	Homeowners	899​	3.3​
IA	Homeowners	899​	3.3​
IA	Homeowners	1162​	2.3​
IA	Homeowners	1162​	2.3​
IA	Homeowners	1162​	2.3​

 

[Crystalyzer]

This is the part of the formula that I think is breaking down when there is more data

(--([@Share]<=[Share])/COUNTIF([Share],[Share]))

It doesn't differentiate the Share amounts by the other criteria and then gives a screwy result once more data is introduced that doesn't match the sample data provided.

 

[Fluff]

Another option

+Fluff 1.xlsm
	A	B	C	D	E
1	State	Line of Business	Share	Group Rank	Column1
2	IA	Homeowners	2303	1	1
3	IA	Homeowners	1162	2	2
4	IA	Homeowners	1162	2	2
5	IA	Homeowners	1162	2	2
6	IA	Homeowners	899	5	3
7	IA	Homeowners	899	5	3
8	IA	Homeowners	899	5	3
9	IA	Homeowners	355	8	4
10	IA	Homeowners	335	9	5
11	IA	Homeowners	264	10	6
12	IA	Homeowners	264	10	6
13	IA	Homeowners	257	12	7
14	IA	rent	899	1	1
15	IA	rent	500	2	2
16	IB	Homeowners	355	1	1
17	IB	rent	335	1	1
18	IB	rent	264	2	2
Lists

Cell Formulas
Range	Formula
D2:D18	D2	=COUNTIFS([State],[@State],[Line of Business],[@[Line of Business]],[Share],">"&[@Share])+1
E2:E18	E2	=SUMPRODUCT((--([@State]=[State])),(--([@[Line of Business]]=[Line of Business])),IFERROR(--([@Share]<=[Share])/COUNTIFS([State],[@State],[Line of Business],[@[Line of Business]],[Share],[Share]),0))