Google Spreadsheet Arrayformula with IF and AND condition-CodePudding

I need some help with google spreadsheet. I tried everything but somehow I am not able to figure out how to solve this.

I have 5 columns, 4 of them are "Blue, Yellow, Green and White" and the other is "Result"

In the Result it should contain the value from the other columns but with some conditions.

In the screenshot I entered the Results manually. I tried everything I am capable of like Arrayforumula(Ifs(and(CONDITIONS)) etc but not working. I spend literally 10h and would really appreciating any help. is that even possible?

Example: https://i.stack.imgur.com/ifpzK.png

CodePudding user response：

This is your screenshot. Now everybody can see it without the need to open it first :)

CodePudding user response：

The following formula in E2 will produce all the required results in the 'Result' column:

=arrayformula(if(mmult(n(istext(B2:D10)),{1;1;1})=0,A2:A10,substitute(trim(transpose(query(transpose(B2:D10),,9^9)))," "," ")))

Making this work with infinite ranges would need more work. It also might not work as expected if both the 'Blue' column and the other three are blank in a given row (I think) - you didn't define this as a possibility.