Have you heard about Logic Gate?  I learned logic gate in high school physics.  Decades ago… so don’t expect I can remember what it is.  😛

Having said that, I do remember the fundamental concept about AND gate and OR gate, which is very helpful for me to master logical functions in Excel.  Before we move to discuss advanced use of SUMPRODUCT as a function to do conditional SUM, we need to understand the behavior of  TRUE and FALSE in Excel first.

You may download a sample file to follow through.

What is TRUE and FALSE in Excel?

Boolean…?  Let’s skip this kind of jargon (the truth is I don’t know it either)…. TRUE or FALSE is simply a result of a logical test:Tip: Did you notice that by default, TRUE/FALSE is center-aligned; while number is right-aligned; text is left-aligned.

TRUE/FALSE is a not number nor text, it is a logical value.

Note: You can input “TRUE” or “FALSE” (without the double quotation, or apostrophe comma before) as logical value directly.

TRUE and FALSE as 1 and 0 respectively

Although TRUE/FALSE are not number, they behave just like 1 (as TRUE) and 0 (as FALSE) when you put them into mathematics operations:

Try substitute TRUE with 1, FALSE with 0, you will get the same results.

However, most FUNCTIONS ignore logical values.

Please study the results carefully.  Although the results highlighted in green seem to be correctly treated TRUE and FALSE as 1 and 0 respectively, it is not the case. The logical values are simply ignored.

How about SUMPRODUCT?  How does it treat logical values?

If you don’t know it, I suggest you read my previous post about the basic of SUMPRODUCT.

“Note 1 – All non-numeric values in array will be treated as 0″

Indeed, this is a common hurdle for many people to use SUMPRODUCT for conditional SUM.

To make SUMPRODUCT work for the above example, we need to convert the array of TRUE/FALSE into an array of 1/0.

HOW?  If you have followed through the content so far, you should be able to figure out many ways by applying simple operations to the array.  E.g.

I have highlighted the common ways for your reference, which is array + 0 and double negative sign (- -) before the array.

No matter which operation you deploy, you will get the same result.

Which one is the best practice?   I don’t have an answer for you.  My preference is double negative because the chance of “unaware” typo is low.  Imagine this: You want to convert the TRUE/FALSE into 1/0 by +0, however you mistype it as +01.  What results are you going to get?

Indeed, the best practice should be evaluating your formula to make sure it works the way you want.

“Perhaps there is no right or wrong… but there is absolutely TRUE or FALSE”.

Let’s dive in SUMPRODUCT in the next post.  Stay tuned. 🙂