Lots of questions for help on this sub, but not as many helpful tips. Here are a couple I wish I learned sooner. The "IF" condition evaluates to 1 if true, and 0 if false. This means you can do math with multiple conditions for and/or logic, rather than nesting if statements. For example:

=IF((A1="RED")*(B1="BLUE"),"PURPLE", "")

Multiplying conditions is the same as saying AND. 1x1=1 (True) 1x0=0 (False)

=IF((A1="RED)*((B1="BLUE")+(B1="CYAN")),"PURPLE,"")

This is saying, if cell A1 is red and cell b1 is blue OR cyan, then purple. Plus is OR logic because 1+0=1 (True). There are other operands you can use to do XOR or other logical operators, as long as you go back to the basic binary math.

Now this is somewhat useful in limiting nested ifs, and I know there's already an AND() function, and OR() function in excel, but those functions don't work in array format.

=SUMIF(B1:B6,A1:A6,">5")
={SUM(IF(A1:A6>5,B1:B6,FALSE))}

The two functions above are the same, but you can see the second form has the advantage when multiple conditions are used. If you don't know you can get the curly braces, an array formula, by pressing ctrl+shift+enter after entering the formula. If you have a large dataset, you can use PERCENTILE(IF( to do an exceedance curve, AVERAGE(IF, MEDIAN(IF. Finally, if you have conditions based on many columns, it can be helpful to name the range of the first column and use OFFSET for the other columns.

For more information see: CPearson - Array Formulas