try 7 ifs with multi sumproduct with multi and + or ....

"driller" wrote:

nice acrobat Epinn, I just wonder guys if you made test on a formula built
with a standard IF (1st) structuring with a sumproduct criterias inside the
true or false results. Maybe u can do such test....for us.

"Epinn" wrote:

Hi Bob and Roger (in alphabetical order),

I just want to let you know that gradually I am detaching Boolean from SUMPRODUCT and I am learning to use Boolean outside of SUMPRODUCT. However, I still need SUMPRODUCT as a stepping stone to write my formula. You won't believe what I did.

Column A = age Column B = eye colour

I have this formula:

=IF(AND(A230,IF(OR(B2="blue",B2="brown"),1,0)=1), TRUE,FALSE)

But I don't like the 1, 0, AND, OR etc. Then I think: how would I write it if it were SUMPRODUCT.

So I wrote my SUMPRODUCT formula using * and +, A2:A10, B2:B10 etc. as if I was doing a count. When I was happy with my SP formula, then I changed it to IF and converted the range back to a single cell (e.g. A2). I ended up with the following:

=IF((A230)*((B2="blue")+(B2="brown")),TRUE,FALSE)

So, this is my "joke" of the day. Next time, I should be able to use Boolean at will without using SP as a "bridge."

Do you prefer the second IF formula to the first? Are there any circumstances when the first formula has an advantage over the second? I should feel free to use Boolean, right?

Please feel free to comment and I don't need any guarantee on your answers. <G

Epinn