Making a Complicated Sumproduct into a Function

**HCLax** · 07-09-2014, 08:53 AM

Hi,

So I have a rather complicated IF, Countif, Sumproduct combo in a formula right now. As you can imagine this makes my worksheet run rather slowly. I was curious if there is any way to make this formula into a User Defined Function. The formula looks like this:

[CODE]
=IF(A15="","",IF((AND(COUNTIFS(J:J,J15,AM:AM,ABS(AM15),AD:AD,AD15)>1,(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)=ABS($BD15)),--($AX$17:$AX$10000=$AX15),$BD$17:$BD$10000))<>ABS(BD15),(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)=ABS($BD15)),--($AX$17:$AX$10000=$AX15),$BD$17:$BD$10000))<>0)),"Duplicate",IF((AND(COUNTIFS(J:J,J15,AM:AM,ABS(AM15),AD:AD,AD15)>1,(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)=ABS($BD15)),--($AX$17:$AX$10000<>$AX15),$BD$17:$BD$10000))<>ABS(BD15),(SUMPRODUCT(--($J$17:$J$10000=$J15),--($AD$17:$AD$10000=$AD15),--(ABS($BD$17:$BD$10000)=ABS($BD15)),--($AX$17:$AX$10000<>$AX15),$BD$17:$BD$10000))<>0)),"Research","")))
[CODE]

I would not be surprised if this isn't possible but thought I would try. Another possible approach would be to make this into a macro and instead of the output being "Duplicate" or "Research" just have the "Duplicate" Cells Highlighted in Red and the "Research" Celss highlighted in yellow.

Thanks,
Justin

**Kaper** · 07-09-2014, 09:41 AM

First try to limit ranges everywhere - it quite often makes formula faster like:

Formula:

Please Login or Register  to view this content.

then think of splitting into smaller pieces and use helper column it seems, that you force excel to calculate the same sumproduct several times - may be calculate it only once in helper column - less elegant but more efficient :-)