Ranking with SUMPRODUCT to ignore blanks

[Celticshadow]

Hi All

I am ranking data using the below formula but if a cell is blank then I would like that cell ignored and only rank cells that contain data

{=SUMPRODUCT(--($A$2:$A$10243=$A2),--(IR$2:IR$10243>IR2))+1}

The A2:A10243 is a column containing an id number for the data held in cells IR2:IR10243.

Thanks in advance.

Regards

 

[Mr_Lucky]

I'm not seeing your data...Try posting some rows with the relevant columns of the table along with expected results

M.

Please find below data

=IF(AND([@[Reported bleed]]<>"",[@[Regulatory reporting impact]]<>"",[@[Test stage_ranking]]<>"",[@[Regulatory reporting impact_ranking]]<>"",[@[Status ranking]]<>""), SUMPRODUCT(--([@[Regulatory reporting impact]]=[Regulatory reporting impact]),--([@[Reported bleed]]<[Reported bleed]))+1,"")

Reported bleed	Regulatory reporting impact	Test stage_ranking	Regulatory reporting impact_ranking	Status ranking	Ranking	Expected result
400	Mis-reporting	Prod	Top 10 Mis-reporting Daily Bleed - Unscheduled	Unscheduled	1	Ranked=1
300		Prod	Top 10 Mis-reporting Daily Bleed - Unscheduled	Unscheduled		No ranking
200	Mis-reporting	Prod	Top 10 Mis-reporting Daily Bleed - Unscheduled	Unscheduled	2	Ranked=2
90	Mis-reporting		Top 10 Mis-reporting Daily Bleed - Unscheduled	Unscheduled		No ranking
80	Mis-reporting	Prod	Top 10 Mis-reporting Daily Bleed - Unscheduled	Unscheduled	4	Ranked=3
70	Mis-reporting	Prod		Unscheduled		No ranking
60	Mis-reporting	Prod	Top 10 Mis-reporting Daily Bleed - Unscheduled	Unscheduled	6	Ranked=4
50	Mis-reporting	Prod	Top 10 Mis-reporting Daily Bleed - Unscheduled			No ranking
40	Mis-reporting	Prod	Top 10 Mis-reporting Daily Bleed - Unscheduled	Unscheduled	8	Ranked=5

<colgroup><col width="121" style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;"> <col width="153" style="width: 115pt; mso-width-source: userset; mso-width-alt: 5595;"> <col width="182" style="width: 137pt; mso-width-source: userset; mso-width-alt: 6656;"> <col width="308" style="width: 231pt; mso-width-source: userset; mso-width-alt: 11264;"> <col width="140" style="width: 105pt; mso-width-source: userset; mso-width-alt: 5120;"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <tbody> </tbody>

 

[Marcelo Branco]

Something like this

	A​	B​	C​	D​	E​	F​
1​	Reported bleed​	Regulatory reporting impact​	Test stage_ranking​	Regulatory reporting impact_ranking​	Status ranking​	Ranking​
2​	400​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	1​
3​	300​		Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​
4​	200​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	2​
5​	90​	Mis-reporting​		Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​
6​	80​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	3​
7​	70​	Mis-reporting​	Prod​		Unscheduled​
8​	60​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	4​
9​	50​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​
10​	40​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	5​

Formula in F2 copied down
=IF(AND(B2<>"",C2<>"",D2<>"",E2<>""),SUMPRODUCT(--(A$2:A$10>A2),--(B$2:B$10=B2),--(C$2:C$10<>""),--(D$2:D$10<>""),--(E$2:E$10<>""))+1,"")

M.

 

[Mr_Lucky]

Something like this

	A​	B​	C​	D​	E​	F​
1​	Reported bleed​	Regulatory reporting impact​	Test stage_ranking​	Regulatory reporting impact_ranking​	Status ranking​	Ranking​
2​	400​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	1​
3​	300​		Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​
4​	200​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	2​
5​	90​	Mis-reporting​		Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​
6​	80​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	3​
7​	70​	Mis-reporting​	Prod​		Unscheduled​
8​	60​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	4​
9​	50​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​
10​	40​	Mis-reporting​	Prod​	Top 10 Mis-reporting Daily Bleed - Unscheduled​	Unscheduled​	5​

<tbody>
</tbody>

Formula in F2 copied down
=IF(AND(B2<>"",C2<>"",D2<>"",E2<>""),SUMPRODUCT(--(A$2:A$10>A2),--(B$2:B$10=B2),--(C$2:C$10<>""),--(D$2:D$10<>""),--(E$2:E$10<>""))+1,"")

M.

That has completely solved the issue.

I am extremely grateful for your help

 

[Marcelo Branco]

You are welcome. Glad to help.

M.