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. 🙂
Is it my poor eyes or you didn’t find it apropriate to attach the Workbook from which you demonstrated the various formulas ?
If I’m not mistaken I suggested, more than once, to attach the WB in order to asloow the readers to excersice the issues.
Before I’ll comment on one of the issues – I’ll wait for the WB.
Have anice weekend,
It will be more than nice if you will adopt it as a habit !
Thank you very much for your reminder again. I have attached the sample file. Please feel free to take a look and comment.
I know I don’t have a habit to attach a sample file yet. I am working hard to improve that.
Looking forward to hearing from you the issue you mentioned.
You too have a nice weekend.
Please try to remember my, useful, proposal in your next posts.
I have one comment regarding the use of SUMPRODUCT while T/F are involved.
Although it will not be exactely the basic format of the Function – one can, easily, replace the comma with an asterisk and it will workת as expectedת without converting the T/F array into a 1/0 array.
I will bare that in mind. 🙂
And thanks for your suggestion on multiplying the two arrays together within the SUMPRODUCT. Indeed, I have that in the sample file, which I plan to dive in the next post, while this post focuses on how the True False works.