locked
array functie RRS feed

  • Question

  • With (in Dutch)    {=SOM(ALS(OF(LINKS(E2:E252;1)="-";E2="");"";((B2:B252)*(E2:E252))))}

    in English (I suppose)   {=SUM(IF(OR(LEFT(E2:E252;1)="-";E2="");"";((B2:B252)*(E2:E252))))}

    I want to get the sum of the products E*B in those cases where the first character of E is not the minus sign (then it is not a number but text beginning with -) and also E is not blank. All B cells contain numbers.

    I'm years "out of training" because of which I don't see what I did wrong.

    Thanks in advance for Your assistance.

    Jack Sons.

    Sunday, November 22, 2020 3:56 PM

All replies

  • In the first place, OR returns a single TRUE/FALSE value, not an array of values. So we have to use another method.

    In the second place, I think you meant E2:E252="" instead of E2="".

    Try this, confirmed with Ctrl+Shift+Enter:

    =SUM(IF((LEFT(E2:E252)="-")+(E2:E252="");"";B2:B252*E2:E252))

    =SOM(ALS((LINKS(E2:E252)="-")+(E2:E252="");"";B2:B252*E2:E252))


    Regards, Hans Vogelaar (https://www.eileenslounge.com)


    Sunday, November 22, 2020 7:10 PM