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

 

[theBardd]

Post an example of your data and required results.

 

[Mr_Lucky]

E	G	F
Names	Age	Country	Ranking	Expected result	Pass/Fail
Jack	25	CAN	1	If name, age, country is not blank then rank.	Pass
	24	UK		If name is blank and age populated and country populated then no ranking.	Pass
	23			If name and country are blank but age populated then no ranking. Correct	Pass
William	22			If name and age is populated but country is blank then ranking should be populated in the right sequence.	Fail

<tbody>
</tbody>

=IF(AND(E1<>"",F1<>""), SUMPRODUCT(--(E1=$E$1:$E$4),--(G3<$G$1:$G$4))+1,"")

 

[theBardd]

This works I believe

=IF(AND(E2<>"",F2<>"",G2<>""), SUMPRODUCT(--(E2=$E$2:$E$6),--(G2<$G$2:$G$6))+1,"")

 

[Mr_Lucky]

Thanks for the quick reply. I can confirm that the formula is working and removing the ranking if either of the blank conditions are met. However, where there is now a blank (inline with expectation) the next number is out of sequence e.g. ranking 1,2,3,4 blank (no ranking), 6. Calculation is not adjusting the numbering sequence when more than blank condition is applied. Is there a way to fix this.

 

[theBardd]

Works fine on my machine.

 

[Mr_Lucky]

I have re-checked the conditions and the below table shows which one is not working. Have I missed something? Also apologies if the previous table was a bit confusing.

When priority is blank the ranking numbers in next line for the impact category are not adjusted.

=IF(AND(B2<>"",C2<>"",D2<>""), SUMPRODUCT(--(B2=$B$2:$B$15),--(C2<$C$2:$C$15))+1,"")

B	C	D	E	F	G
Impact	Volume	Priority	Ranking	Pass/Fail	Expected result
Mis-reporting	500	Priority_1	1	Pass	1
	480	Priority_2		Pass	Blank
Mis-reporting	470			Pass	Blank
	460			Pass	Blank
Mis-reporting	450	Priority_5	3	Fail	2
	600	Priority_1		Pass	Blank
Under-reporting	590	Priority_2	1	Pass	1
Under-reporting	580	Priority_3	2	Pass	2
Under-reporting	570			Pass	Blank
Under-reporting	560	Priority_5	4	Fail	3
Over-reporting	400	Priority_1	1	Pass	1
	390	Priority_2		Pass	Blank
Over-reporting	370			Pass	Blank
Over-reporting	360	Priority_5	3	Fail	2

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 141px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Mis-reporting

<tbody>
</tbody>

</body>

 

[Marcelo Branco]

Try

G2 copied down
=IF(COUNTA(B2:D2)=3,COUNTIFS(B$2:B$15,B2,B$2:B$15,"<>",C$2:C$15,">"&C2,D$2:D$15,"<>")+1,"")

Hope this helps

M.

 

[Mr_Lucky]

Thanks. The formula works really well

I have noticed a couple of things:
1) if two issues have the same volume and have the same impact they get the same ranking score. I don't suppose there is way to adjust for this.

2) the ranking calculation references a cell with a formula in but there is no value in the cell other then the formula. Excel things that this cell is not blank. I have seen various answers to this issue by by selecting blank cells, paste value, populate with a random value "@" and find and delete "Q". This works but is and unwanted additional step. There is also another option by going to special, formula, select text and then delete. Both options work but reduces the level of automation for the calculations.

 

[Marcelo Branco]

Try to post a data sample along with expected results illustrating scenarios 1 and 2

M.

 

[Mr_Lucky]

1) Two line items ranked are both ranked 2 and require a tie-breaker for one to be ranked 2 and the other 3

E	I	F	H	N	M
Impact_calculated	Impact	Volume	Priority	Ranking	Expected result
IF('Priority category'!$I21="Mis-reporting","Mis-reporting", IF('Priority category'!$I21="Under-reporting", "Under-reporting",IF('Priority category'!$I21="Over-reporting", "Over-reporting",IF('Priority category'!$I21="Other", "Other",""))))	Mis-reporting	250	Priority_2	2	Ranking: 2 or 3
IF('Priority category'!$I21="Mis-reporting","Mis-reporting", IF('Priority category'!$I21="Under-reporting", "Under-reporting",IF('Priority category'!$I21="Over-reporting", "Over-reporting",IF('Priority category'!$I21="Other", "Other",""))))	Mis-reporting	250	Priority_2	2	Ranking: 2 or 3
IF('Priority category'!$I21="Mis-reporting","Mis-reporting", IF('Priority category'!$I21="Under-reporting", "Under-reporting",IF('Priority category'!$I21="Over-reporting", "Over-reporting",IF('Priority category'!$I21="Other", "Other",""))))	Mis-reporting	300	Priority_2	1	Ranking: 1

<tbody>
</tbody>

2) If the field impacted calculation is blank (not returned a value from "I" impacted) but the cell still contain the formula and excel things that the cell is not blank

E	I	F	H	N	M
Impact_calculated	Impact	Volume	Priority	Ranking	Expected result
IF('Priority category'!$I21="Mis-reporting","Mis-reporting", IF('Priority category'!$I21="Under-reporting", "Under-reporting",IF('Priority category'!$I21="Over-reporting", "Over-reporting",IF('Priority category'!$I21="Other", "Other",""))))		400	Priority_1	2	No ranking

<tbody>
</tbody>