Speed up sumproduct for large data range

[cmb80]

I am currently using sum product as the code suggests

=IF(E2="NA","",SUMPRODUCT((E:E<E2)*(G2=G:G))+1)

This works fine for what I want, but I have over 200,000 rows of data. Is there any improvements of alternatives Ican make to my code to quicken things up?

Basically I am ranking what is in column E - (based on the criteria in G) - unless in E is NA which then returns a blank cell for that row

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[cmb80]

I have a "cut down" example attached. Column H (highlighted) shows my existing code. I only have 300 odd rows in the example and the calculation still takes around 10 seconds.

The full data set has over 200,000 rows - which is why I ask if the formula can be improved or altered to speed things up

[joeu2004]

I am currently using sum product as the code suggests
=IF(E2="NA","",SUMPRODUCT((E:E<E2)*(G2=G:G))+1)
This works fine for what I want, but I have over 200,000 rows of data. Is there any improvements of alternatives Ican make to my code to quicken things up?

First, get out of the habit of specifying whole-column ranges like E:E and G:G. In Excel 2007 and later, that causes SUMPRODUCT to process arrays of 1,048,576 elements. So at a minimum, change the formula to:

=IF(E2="NA","",SUMPRODUCT(($E$1:$E$300000<E2)*(G2=$G$1:$G$300000))+1)

Change 300000 appropriately. Also change $1 to $2 if appropriate (small detail).

Second, if you do not require XL2003 compatibility (not saving to "xls" file), use COUNTIFS as follows:

=IF(E2="NA","",COUNTIFS($E$1:$E$300000,"<"&E2,$G$1:$G$300000,G2)+1)

Basically I am ranking what is in column E - (based on the criteria in G) - unless in E is NA which then returns a blank cell for that row

FYI, I have not given any thought to whether there is a better way to do this. I suspect there is.

[cmb80]

Thanks, I will give that a go