SOLVED

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

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3152119%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3EHow%20to%20simulate%20old%20array%20formulas%20that%20yield%20one%20result%20instead%20of%20spilling%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3152119%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHow%20to%20implement%20the%20following%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ESUM(IF(LEFT(A201%3AA210)%3D%22CAPITAL%22%2CC201%3AC210%2C0))%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3Ewhich%20will%20add%20values%20in%20C201%20through%20C210%20when%20A201%20through%20A210%20has%20the%20value%20%22CAPITAL%22%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3152119%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3152140%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20simulate%20old%20array%20formulas%20that%20yield%20one%20result%20instead%20of%20spilling%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3152140%22%20slang%3D%22en-US%22%3ESUM(IF(LEFT(A201)%3D%22CAPITAL%22%2CC201%2C0))%20and%20copy%20down%20to%20row%20210.%20Or%2C%20I%20think%20this%20would%20also%20work%20(%40%20is%20the%20intersection%20operator).%20SUM(IF(LEFT(%40A201%3AA210)%3D%22CAPITAL%22%2C%40C201%3AC210%2C0))%3C%2FLINGO-BODY%3E
New Contributor

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

@JMB17 

*

@ 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...

 

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

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

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

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

 

harshulz_0-1644543703429.png

 

@harshulz 

 

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

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
Try this:
=SUMPRODUCT(--(ISNUMBER(SEARCH("CAPITAL",A282:A290))),B282:B290)