Forum Discussion
How to simulate old array formulas that yield one result instead of spilling
It should be Left(A201:A210, 7). If omitted, it defaults to one - I just copied the existing formula and did not catch that. But, to be honest, I'm not sure I entirely understand the question. It does not appear the existing formula should spill, with the sum function.
Perhaps this is closer to what you are trying do?
=IF(LEFT(@A201:A210, 7)="CAPITAL",@C201:C210,0)Or, perhaps enter the existing formula with Ctrl+Shift+Enter, or replace sum with sumproduct?
SUMPRODUCT(--(LEFT(A201:A210, 7)="CAPITAL"),C201:C210)
- May I know whether the function LEFT(A201:A210) works as your expectation while the number of character is not passed to the function?
It should be Left(A201:A210, 7). If omitted, it defaults to one - I just copied the existing formula and did not catch that. But, to be honest, I'm not sure I entirely understand the question. It does not appear the existing formula should spill, with the sum function.
Perhaps this is closer to what you are trying do?
=IF(LEFT(@A201:A210, 7)="CAPITAL",@C201:C210,0)Or, perhaps enter the existing formula with Ctrl+Shift+Enter, or replace sum with sumproduct?
SUMPRODUCT(--(LEFT(A201:A210, 7)="CAPITAL"),C201:C210)
It is in the support article for the function:
If num_chars is omitted, it is assumed to be 1.
Yes, I know it.
But how can a 1-length string be equal to "CAPITAL"?
*
@ is the intersection operator
To be more precise:
SPACE is the intersection operator.
@ is the reference operator aka implicit intersection operator.
