SOLVED

New Contributor

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

How to implement the following

SUM(IF(LEFT(A201:A210)="CAPITAL",C201:C210,0))

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

11 Replies

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

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))

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

*

@ is the intersection operator

To be more precise:

SPACE is the intersection operator.

@ is the reference operator aka implicit intersection operator.

https://support.microsoft.com/en-us/office/calculation-operators-and-precedence-in-excel-48be406d-49...

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

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

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

It is in the support article for the function:

If num_chars is omitted, it is assumed to be 1.

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

Yes, I know it.

But how can a 1-length string be equal to "CAPITAL"?

best response confirmed by radinhj (New Contributor)
Solution

# Re: 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)

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

You are correct.

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

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

@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

=SUMPRODUCT((A201:A210="CAPITAL")*(C201:C210))

=SUMIF(A201:A210,"CAPITAL",C201:C210)

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

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)

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

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

sum(if(find("CAPITAL",UCASE(A282:A290)>0,B282:B290,0))

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

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

Try this:
=SUMPRODUCT(--(ISNUMBER(SEARCH("CAPITAL",A282:A290))),B282:B290)