Ranking with SUMPRODUCT to ignore blanks

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
377
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
377
Office Version
  1. 365
Platform
  1. Windows
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

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
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,022
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Celticshadow

Active Member
Joined
Aug 16, 2010
Messages
377
Office Version
  1. 365
Platform
  1. Windows
Hi Marcelo

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

Regards
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,022
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 

Mr_Lucky

New Member
Joined
Jul 31, 2019
Messages
27
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.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
17,022
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Maybe...

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

M.
 

Mr_Lucky

New Member
Joined
Jul 31, 2019
Messages
27
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?
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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?

Try OR instead of AND
 

Mr_Lucky

New Member
Joined
Jul 31, 2019
Messages
27
Try OR instead of AND

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,"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,199
Messages
5,835,936
Members
430,396
Latest member
dzifna

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top