Need assistance to make Formula cal faster

[Pi*]

Good morning,

This formula works but takes an excessive amount of time for one caluclation, and I need this for multiple row critera. So, can this calculation be changed in order to get the same result with faster calculation time? I am using Excel 2010.

=IF(COUNTIFS(A:A,A2,N:N,"2000002487/")+COUNTIFS(A:A,A2,N:N,"507/BFM901292")=2,"CABINET",IF(COUNTIFS(A:A,A2,N:N,"APXV9ERR18-C-A20")+COUNTIFS(A:A,A2,N:N,"APXVSPP18-C-A20")=2,"ANTENNA",IF(COUNTIFS(A:A,A2,N:N,"KRC131146/1")=1,"TOWER",IF(COUNTIFS(A:A,A2,N:N,"KRC161287/2")=1,"TOWER",IF(COUNTIFS(A:A,A2,N:N,"GPS-TMG-HR26NCM")=1,"GROUND MOUNT",IF(COUNTIFS(A:A,A2,N:N,"RPM2532891/10M")=1,"CIVIL GROUND",IF(COUNTIFS(A:A,A2,Y:Y,25070003)=1,"CLAIMS")))))))

Regards,

[Pete_UK]

Do you really need to use full-column references? Do you really have a million rows of data that you need to check through?

Pete

[Pi*]

Preferably since the specific Part numbers I'm looking for could appear multi times in >300K lines of data.

It's a huge report. However I'll take any suggestions you may have.

I appreciate your quick responce. ty

[MrShorty]

I might suggest seeing if there is a different way to tackle the problem. In this thread, http://www.excelforum.com/excel-form...41#post3111341, benishiryo and I looked at a specific example he had created for testing calculation speed. I came up with a different approach using a helper column, that dramatically decreased the time needed for calculation (30 s for the array formulas to near 1-2 s using a helper column).

I'm not very good with these kind of single cell array formulas. It looks like your formula is looking in column A for whatever is in A2 (single occurrence???) and for different text strings in N, then trying to return some related text when it finds A2 in A combined with each text string in N. Perhaps there is a better way to structure the database and/or query (perhaps combined with a lookup table) that could dramatically reduce the number of operations needed by the function.

[Pi*]

Will do. I appreciate your suggestion.