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)

11 Replies

  • radinhj's avatar
    radinhj
    Copper Contributor
    Feb 11, 2022
    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
    • JMB17's avatar
      JMB17
      Bronze Contributor
      Feb 11, 2022
      Try this:
      =SUMPRODUCT(--(ISNUMBER(SEARCH("CAPITAL",A282:A290))),B282:B290)
  • harshulz's avatar
    harshulz
    Iron Contributor
    Feb 11, 2022

    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)

     

     

    • radinhj's avatar
      radinhj
      Copper Contributor
      Feb 11, 2022

      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)

       

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

Resources