Forum Discussion

radinhj's avatar
radinhj
Copper Contributor
Feb 10, 2022
Solved

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"
excel
  • JMB17's avatar
    JMB17
    Feb 11, 2022

    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)

JMB17's avatar
JMB17
Bronze Contributor
Feb 10, 2022
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))
Willy Lau's avatar
Willy Lau
Iron Contributor
Feb 11, 2022
May I know whether the function LEFT(A201:A210) works as your expectation while the number of character is not passed to the function?
  • JMB17's avatar
    JMB17
    Bronze Contributor
    Feb 11, 2022

    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)

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Feb 11, 2022

    Willy Lau 

    It is in the support article for the function:

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

    • Willy Lau's avatar
      Willy Lau
      Iron Contributor
      Feb 11, 2022

      Yes, I know it.

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

Resources