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]

Using Office 365 it works out of the box. Seems like you found a solution that is not compatible with your version of Excel.

Book2
	A	B	C	D
1	State	Line of Business	Share	Group Rank
2	IA	Homeowners	1162	8
3	IA	Homeowners	1162	8
4	IA	Homeowners	1162	8
5	IA	Homeowners	2303	7
6	IA	Homeowners	257	6
7	IA	Homeowners	264	5
8	IA	Homeowners	264	5
9	IA	Homeowners	264	5
10	IA	Homeowners	335	4
11	IA	Homeowners	335	4
12	IA	Homeowners	355	3
13	IA	Homeowners	355	3
14	IA	Homeowners	500	2
15	IA	Homeowners	899	1
16	IA	Homeowners	899	1
17	IA	Homeowners	899	1
18	IA	Homeowners	899	1
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=SUMPRODUCT((--([@State]=[State])),(--([@[Line of Business]]=[Line of Business])),(--([@Share]<=[Share]))/COUNTIF([Share],[Share]))

 

[Fluff]

@Crystalyzer do your results actually make sense to you?

 

[azdraon]

Using Office 365 it works out of the box. Seems like you found a solution that is not compatible with your version of Excel.

Book2
	A	B	C	D
1	State	Line of Business	Share	Group Rank
2	IA	Homeowners	1162	8
3	IA	Homeowners	1162	8
4	IA	Homeowners	1162	8
5	IA	Homeowners	2303	7
6	IA	Homeowners	257	6
7	IA	Homeowners	264	5
8	IA	Homeowners	264	5
9	IA	Homeowners	264	5
10	IA	Homeowners	335	4
11	IA	Homeowners	335	4
12	IA	Homeowners	355	3
13	IA	Homeowners	355	3
14	IA	Homeowners	500	2
15	IA	Homeowners	899	1
16	IA	Homeowners	899	1
17	IA	Homeowners	899	1
18	IA	Homeowners	899	1
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=SUMPRODUCT((--([@State]=[State])),(--([@[Line of Business]]=[Line of Business])),(--([@Share]<=[Share]))/COUNTIF([Share],[Share]))

What The?!?!? It does seem to work on smaller amounts of data. I would be curious if I was able to upload the entire data set if it still works for you. I will need ot use another computer though.

 

[azdraon]

What The?!?!? It does seem to work on smaller amounts of data. I would be curious if I was able to upload the entire data set if it still works for you. I will need ot use another computer though.

Yeah

Using Office 365 it works out of the box. Seems like you found a solution that is not compatible with your version of Excel.

Book2
	A	B	C	D
1	State	Line of Business	Share	Group Rank
2	IA	Homeowners	1162	8
3	IA	Homeowners	1162	8
4	IA	Homeowners	1162	8
5	IA	Homeowners	2303	7
6	IA	Homeowners	257	6
7	IA	Homeowners	264	5
8	IA	Homeowners	264	5
9	IA	Homeowners	264	5
10	IA	Homeowners	335	4
11	IA	Homeowners	335	4
12	IA	Homeowners	355	3
13	IA	Homeowners	355	3
14	IA	Homeowners	500	2
15	IA	Homeowners	899	1
16	IA	Homeowners	899	1
17	IA	Homeowners	899	1
18	IA	Homeowners	899	1
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=SUMPRODUCT((--([@State]=[State])),(--([@[Line of Business]]=[Line of Business])),(--([@Share]<=[Share]))/COUNTIF([Share],[Share]))

yeah, the rankings you display arent correct either. Its all over the place like mine.

 

[Fluff]

How about

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

Cell Formulas
Range	Formula
D2:D18	D2	=COUNTIFS([State],[@State],[Line of Business],[@[Line of Business]],[Share],">"&[@Share])+1

 

[Crystalyzer]

@Crystalyzer do your results actually make sense to you?

Since you mentioned it, no. So went back and found that the data I copied in wasn't being handled as numeric in all cases so I corrected this and the results are as follows:

Book2
	A	B	C	D
2	IA	Homeowners	2,303	1
3	IA	Homeowners	1,162	2
4	IA	Homeowners	1,162	2
5	IA	Homeowners	1,162	2
6	IA	Homeowners	899	3
7	IA	Homeowners	899	3
8	IA	Homeowners	899	3
9	IA	Homeowners	899	3
10	IA	Homeowners	500	4
11	IA	Homeowners	355	5
12	IA	Homeowners	355	5
13	IA	Homeowners	335	6
14	IA	Homeowners	335	6
15	IA	Homeowners	264	7
16	IA	Homeowners	264	7
17	IA	Homeowners	264	7
18	IA	Homeowners	257	8
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=SUMPRODUCT((--([@State]=[State])),(--([@[Line of Business]]=[Line of Business])),(--([@Share]<=[Share])/COUNTIF([Share],[Share])))

This makes more sense since it is now ranking by the Share in Descending order.

 

[azdraon]

How about

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

Cell Formulas
Range	Formula
D2:D18	D2	=COUNTIFS([State],[@State],[Line of Business],[@[Line of Business]],[Share],">"&[@Share])+1

Unfortunately I am trying to get it so it repeats Rank if values are equal to each other. This appears to then not give be duplicate rank values.

 

[Fluff]

It does for me, if you look at rows 3,4 & 5 they are all ranked 2

 

[Fluff]

@Crystalyzer try changing one of the values in cols A and/or B