In Excel, using two minus signs next to each other (--) converts "TRUE" to 1 and "FALSE" to 0.
Example : Let's take a simple logical condition : 3 > 2.
Apply this in Excel = 3 > 2 returns "TRUE". Using "--" (two minus signs together) with this condition returns 1 = --(3 > 2).
This method is commonly used in SUMPRODUCT formulas to convert conditional arrays that evaluate to TRUE/FALSE into 1/0. The SUMPRODUCT formula ignores non-numeric cells. Using the double minus signs technique, we can command Excel to convert TRUE/FALSE to 1/0.
Let's say you have the following values in cells A1:B8.
| A | B | |
|---|---|---|
| 1 | X | Y |
| 2 | 20 | 2 |
| 3 | 10 | 5 |
| 4 | 5 | 8 |
| 5 | 15 | 11 |
| 6 | 7 | 14 |
| 7 | 8 | 17 |
| 8 | 13 | 20 |
Example : Suppose you are asked to calculate the sum of the values in the range B2:B8, but only for those rows where the value in the range A2:A8 is less than 12. It effectively filters out rows where A2:A8 is greater than or equal to 12.
Use this formula : =SUMPRODUCT(--(A2:A8<12),B2:B8)
- (A2:A8<12): This part checks if each value in the range A2:A8 is less than 12 and creates an array of TRUE/FALSE values based on the condition.
- The first minus sign converts the array of TRUE/FALSE values to
(0,-1,-1,0,-1,-1,0), and the second minus sign multiplies each value by the negative sign. This results in(0,1,1,0,1,1,0). This is basic math: (-) * (-) = +. - SUMPRODUCT: This function multiplies corresponding elements of the two arrays obtained in the previous step and B2:B8. These are the two arrays: (0,1,1,0,1,1,0) and (2,5,8,11,14,17,20).
- The formula evaluates
=SUMPRODUCT((0,1,1,0,1,1,0),(2,5,8,11,14,17,20))and returns a value of 44.
Here's how the calculation works:
- 0 * 2 = 0
- 1 * 5 = 5
- 1 * 8 = 8
- 0 * 11 = 0
- 1 * 14 = 14
- 1 * 17 = 17
- 0 * 20 = 0
Now, the SUMPRODUCT function adds up these products:
0 + 5 + 8 + 0 + 14 + 17 + 0 = 44
In the SUMPRODUCT function in Excel, the multiply sign (*) is used to perform element-wise multiplication between arrays. You can calculate the same task of calculating the sum of products for a given range of cells based on certain conditions using the following formula:
=SUMPRODUCT((A2:A8<12)*B2:B8)-
Evaluate the condition
(A2:A8 < 12):
The result of the condition(A2:A8 < 12)would be the following array: [FALSE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE] -
Evaluate the expression
(A2:A8 < 12) * B2:B8:
The result of the expression(A2:A8 < 12) * B2:B8would be the following array: [0, 5, 8, 0, 14, 17, 0] -
Calculate the sum of the array obtained in Step 2:
Summing the array [0, 5, 8, 0, 14, 17, 0] results in: 5 + 8 + 14 + 17 = 44
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
Superb. Thanks for sharing your knowledge.
ReplyDeleteCan you please also add one of the most power full excel tool "Turning Data into Decisions" i.e. PivotTable, PowerPivot and PivotCharts?
Really good
ReplyDeleteSo in the first example we are wanting to add the values in column B where the value in column A is less than 12.
ReplyDeleteYour example looks confusing to me. Could you elaborate what you are this formula exactly is doing please? Formula With minus in first array and without in the second formula?
ReplyDelete