sumproduct with dynamic range

[RSHA]

I'm tryping to puopulate the IF condtion with sumproduct. I’ve tried to use SumProduct formula manually for a while , but my workload is getting heavier and I have to process this same task many times. I’m trying to create a Macro for this task but not sure how to approach it since the number of rows can expand based on the request.

Below is the code in Excel and macro recorded code that I manually use:

=IF(SUMPRODUCT((C2<=$D$2:$D$10000)*(D2>=$C$2:$C$10000)*(A2=$A$2:$A$10000))>1,"Yes","No")
"=IF(SUMPRODUCT((RC[-2]<=R2C4:R10000C4)*(RC[-1]>=R2C3:R10000C3)*(RC[-4]=R2C1:R10000C1))>1,""Potential Overlap"",""Non Potential Overlap"")"

Can i have a helping hand to make this with dynamic range. Thanks in Advance

 

[GraH - Guido]

Perhaps you can make the dynamic range like
= $D$2:INDEX($D:$D,COUNTA($D:$D))
= $C$2:INDEX($C:$C,COUNTA($C:$C))
= $A$2:INDEX($A:$A,COUNTA($A:$A))
and have these in the name manager. In VBA you refer to these named ranges and not the ranges.

In terms of speed I'm not sure at all that will make a great difference. The VBA Ninja's could probably suggest something way better. Perhaps you need to provide a sanitized sample file.

Another thing, I can't help but think you are making a complicated formula for something simple. Might not be needing VBA if it is only about speed.

 

[Marc L]

As I never have seen any dynamic range in more than 20 years I wanna see yours ! ​

​