Entering the SUMPRODUCT formula
Don’t forget the double minus trick and the brackets around the expression. The two minus signs coerce the logical values to ones and zeros. If you select the cell where you have the formula you can see how it works internally by clicking Formulas, Evaluate Formula then click the Evaluate button. The first array evaluates to Boolean TRUE and FALSE values. You will see that there are three TRUE values, the three cases where we have “Red” in A2:A8. This TRUE then evaluates to numeric 1 and is used to multiply the values in the B2:B8 range and then, finally, return the total.
But you don’t need to know how it works internally, you just want it to do your calculations. So, whenever you need it, you just do a copy and paste. Once you have working examples of viable formulas you can easily adapt them to your own needs. I am going to stick to the minus, minus form for most of the subsequent examples, because I think it keeps the Boolean expression clearer, especially where it is complex.