trying to simplify an Array formula

[Andre Croteau]

Hello,

I am trying to simplify this array formula :

=sum((A5:A50=b1)*b5:b50)+sum((A5:A50=b2)*b5:b50)+sum((A5:A50=b3)*b5:b50)+sum
((A5:A50=b4)*b5:b50)

Can I replace these 4 distinct section by one?

I tried to enter this array, but I get : "#Value"

=sum((A5:A50=(or(b1,b2,b3,b4))*b5:b50)

Is their a solution out there somewhere?

Thanks in advance.

André

 

[Frank Kabel]

Hi
try
=SUMPRODUCT(((A5:A50=B1)+(A5:A50=B2)+(A5:A50=B3)+(A5:A50=B4))*(B5:B50))
as non array function

 

[Andre Croteau]

Hi Frank,

Thanks for your reply.
I wanted to simplyfy my original question, but in reality, I had more
criterias in my formula, needing an array formula.

I was looking if there was a way to use the "OR" function within the array
formula to look within the list of items in cells B1:B4

André

 

[Frank Kabel]

Hi Andre
try the following array formula (entered with CTRL+SHIFT+ENTER):
=SUMPRODUCT((A5:A50=TRANSPOSE(B1:B4))*(B5:B50))

 

[Andre Croteau]

This is EXACTLY what I was hoping for
Thank you so much!

André