How to simulate old array formulas that yield one result instead of spilling

New Contributor

How to implement the following



which will add values in C201 through C210 when A201 through A210 has the value "CAPITAL"

11 Replies
SUM(IF(LEFT(A201)="CAPITAL",C201,0)) and copy down to row 210. Or, I think this would also work (@ is the intersection operator). SUM(IF(LEFT(@A201:A210)="CAPITAL",@C201:C210,0))



@ is the intersection operator

To be more precise:

SPACE is the intersection operator.

@ is the reference operator aka implicit intersection operator.


May I know whether the function LEFT(A201:A210) works as your expectation while the number of character is not passed to the function?

@Willy Lau 

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"? 

best response confirmed by radinhj (New Contributor)

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)

@Willy Lau 

You are correct.

But to be fair @JMB17 only used the formula given by @radinhj .


@radinhj IF i am not wrong you want the cell in C to sum up corresponding to column A contains CAPITAL?

if it is directly use sumif OR SUMPRODUCT function








Thanks.  This only works if the value is CAPITAL but not if the value contains CAPITAL.

The question I posed is somewhat simplified since i am actually looking to include multiple complex conditions (using AND and OR within the IF statement)


I apologize for the sloppiness if omitting the ",7" for field length. I was trying to simplify the problem. Thanks to everyone who responded.

The actual issue is a bit more complicated.

here is an example of the formula I am trying to enter


that is, sum the amounts in cells B282 through B290 when the corresponding value in A282 through A290 contains "CAPITAL" (ignoring case)

inserting a @ sign did not work
Try this: