Multiply 2 arrays with an if condition

New Contributor

Hi all,

 

I have 2 arrays like below:

 def
array1                 47                53
                 90              129                97
               162              204              210

 

 bc
array2              -        40,727              -  
               -                -        55,608
       92,680              -                -  

 

What i need to do a sumproduct of the 2 arrays where - when the value in array2 is greater than 50,500, the formula is array1value*array2value*(1-0.5)

 

so 1 product is : 47*40,727 

but for product where value > 50,000, product is  97*55607*(1-0.5)

 

Is this possible? really hope this makes sense

2 Replies
Hi, think i just solved it. =SUMPRODUCT(--(D38:F46>100000),(D38:F46)*(1-0.05),D25:F33). I just have to have a formula for <100K and >=100k and add them
Is there a way to do it in 1 formula? I tried this with solver and its giving me a linearity error