# Ranking with SUMPRODUCT to ignore blanks

##### Active Member
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.

Regards

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.

Regards

Hi All

Perhaps an example may help a little

 ID Data Rank 1 44 2 1 46 1 1 1 32 3 2 34 2 2 56 1 2 2 2 18 3

<tbody>
</tbody>

Hope that helps and many thanks.

Regards

Hi

Maybe...

Excel 2007 or higher
=IF(IR2<>"",COUNTIFS(\$A\$2:\$A\$10243,A2,\$IR\$2:\$IR\$10243,">"&IR2)+1,"")

Excel 2003
=IF(IR2<>"",SUMPRODUCT(--(\$A\$2:\$A\$10243=A2),--(\$IR\$2:\$IR\$10243>IR2))+1,"")

M.

Hi Marcelo

Once again a perfect solution. Many thanks for your help it is very much appreciated.

Regards

Hi Marcelo

Once again a perfect solution. Many thanks for your help it is very much appreciated.

Regards

You are welcome and thanks for the feedback.

M.

I have used this formula to not rank if the reference cell was blank and it works well.

Then I have tried to include two blank condition =IF(OR(A2<>””,B2<>””),SUMPRODUCT...

The problem with this is that the second blank condition still adds a ranking number even though the reference cell is blank. Is there a way that I can apply a second blank condition to the formula.

Any help would be appreciated.

Maybe...

=IF(AND(A2<>"",B2<>""),SUMPRODUCT...

M.

Thanks for reply. I have tried "AND" and it does not rank if either of the blank conditions are met.

The problem now is that if the second blank condition is met then the ranking numbers are not adjusted. Example ranking 1,2,3,4,6. 5 is missing because the second blank condition has been met but the ranking sequence is not adjusted.

=IF(AND(E3<>"",K3<>""), SUMPRODUCT(--(E3=\$E\$3:\$E\$42),--(G3<\$G\$3:\$G\$42))+1,"")

Any ideas in how to fix this?

Thanks for reply. I have tried "AND" and it does not rank if either of the blank conditions are met.

The problem now is that if the second blank condition is met then the ranking numbers are not adjusted. Example ranking 1,2,3,4,6. 5 is missing because the second blank condition has been met but the ranking sequence is not adjusted.

=IF(AND(E3<>"",K3<>""), SUMPRODUCT(--(E3=\$E\$3:\$E\$42),--(G3<\$G\$3:\$G\$42))+1,"")

Any ideas in how to fix this?

I have tried "OR" but than neither of the blank conditions are met. I have also tried (AND(OR and (OR(AND but none of them work.

=IF(AND(E3<>"",K3<>""), SUMPRODUCT(--(E3=\$E\$3:\$E\$42),--(G3<\$G\$3:\$G\$42))+1,"")

=IF(OR(E3<>"",K3<>""), SUMPRODUCT(--(E3=\$E\$3:\$E\$42),--(G3<\$G\$3:\$G\$42))+1,"")

=IF(AND(OR(E3<>"",K3<>"")), SUMPRODUCT(--(E3=\$E\$3:\$E\$42),--(G3<\$G\$3:\$G\$42))+1,"")

=IF(OR(AND(E3<>"",K3<>"")), SUMPRODUCT(--(E3=\$E\$3:\$E\$42),--(G3<\$G\$3:\$G\$42))+1,"")

Replies
1
Views
168
Replies
3
Views
198
Replies
1
Views
193
Replies
2
Views
175
Replies
3
Views
154

1,218,576
Messages
6,143,314
Members
450,477
Latest member
teresab543

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back