How to distinguish if sumproduct needs to be inserted with CSE or not?

Question

I sometimes use the inherent array functionality of sumproduct to avoid havingt to enter formulas with Control + Shift + Enter. But it isn't always working. For example

=SUMPRODUCT((LEN(B2:F2)-LEN(SUBSTITUTE(B2:F2,M$2:M$10,"")))*N$2:N$10)

would work, while

=SUMPRODUCT(--IF(ISNUMBER(N6:N9),N6:N9))

would not.

It isn't really obvious to me, why the first one gives correct results, while the second does not.

Answer 1

Nice question. Rule of thumb for me > You see IF and a range to be analized > you press CSE.

Why? Some functions handle CSE natively for you (SUMPRODUCT being one of them), but others do not, for example SUM but definately also IF. Have a look here and here. Bottom line of the theory (AFAIK) is that CSE will disable something called "implicit intersection" which is explained here. It comes down to:

"Implicit intersection occurs when a range is passed to a function that expects a scalar (single) value. In this situation, Excel will try to resolve the formula using a reference in the same row, or in the same column......Entering an array formula with Control + Shift + Enter (CSE) explicitly disables the implicit intersection behavior. This makes it possible to create formulas that manipulate multiple values input as ranges."

Because you use IF, it doesn't matter it's within SUMPRODUCT. You'll still need to press CSE to disable the native "implicit intersection" that comes with using IF.

---

FWIW: Some additional information on the behaviour called "implicit intersection".

Let's imagine the following data:

I created a named range called Vals from the range A2:C2. Now the formula in B5 is simply =Vals but the result is Val5. Meaning implicit intersection returned the value from my named range that intersected with the column I entered the formula in.

Why? Because in the background (unseen) Excel used the implicit intersection operator ("@") to return a single value from the intersection just mentioned. Would I use CSE (read, removing the logical operator), the value returned would be Val2 (top-left value in the array).

"Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value."

The logical operator "@" will prevent the return of an array and makes sure you'll get a single value returned. Removing this logical operator (is what we do by pressing CSE, or by using functions that do so natively) will make the formula return the array.

You may not see/know about this operator but with the comming of dynamic array formulas they will be in your formulas a lot more. See this MS-documentation on the matter. With those new functionalities, removing the logical operator will not only return the array, it will actually spill the values to neighboring cells. Hence the term "Dynamic array formulas". So you can see the new dynamic array formulas as an automated alternative for legacy CSE-Formulas with the addition to have a spill function amongst others.

---

So to conclude:

Your second formula could also be written:

=@SUMPRODUCT(--@IF(@ISNUMBER(N6:N9),N6:N9))

Pressing Enter does not work because only SUMPRODUCT natively cancels out the (unseen) logical operator, while IF only expects a scalar (single) value. So, unseen but effectively, your formula looks like:

=SUMPRODUCT(--@IF(@ISNUMBER(N6:N9),N6:N9))

However, pressing Control + Shift + Enter will indeed rule out the logical operator and effectively make your formula look like:

=SUMPRODUCT(--IF(ISNUMBER(N6:N9),N6:N9))

And thus being able to take arrays. Hopefully that clarified why you needed to press CSE with your second IF formula.

---

_{Fun fact: Next time, try to write =@SUMPRODUCT(... or =@IF(.... You'll notice that the formula is accepted but the logical operator disappears. A sign that this operator is used in the background =)}