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:

=IF(A15="","",IF((AND(COUNTIFS(J$1:J$10000,J15,AM$1:AM$10000,ABS(AM15),AD$1:AD$10000,AD15)>1, etc.

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 :-)