summing

sonsjack
NewLounger
Posts: 22
Joined: 02 Jun 2011, 21:46

summing

Post by sonsjack »

In B2:B252 all cells contain numbers. In E2:E252 cells contain either a number or a blank or text with - (minus sign) as its leading character.
I want to get the sum of the products of B and E cels in the same row of which the content is a number. My array formula below won't work.

ïn Dutch ={=SOM(ALS(EN(LINKS(E2:E252)<>"-";E2:E252<>"");(B2:B252)*(E2:E252);"";))}

in English (?) ={SUM(IF(AND(LEFT(E2:E252)<>"-";E2:E252<>"");(B2:B252)*(E2:E252);"";))}

I'm rather long "out of training", I can't find what's wrong, so please help.

Jack Sons.

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: summing

Post by HansV »

This is similar to your question in the Microsoft forums array functie. AND returns a single TRUE/FALSE value, so we have to use a different method.
Confirm the following formula with Ctrl+Shift+Enter to turn it into an array formula:

=SUM(IF((LEFT(E2:E252)<>"-")*(E2:E252<>""),B2:B252*E2:E252))

In Dutch:

=SOM(ALS((LINKS(E2:E252)<>"-")*(E2:E252<>"");B2:B252*E2:E252))
Best wishes,
Hans

sonsjack
NewLounger
Posts: 22
Joined: 02 Jun 2011, 21:46

Re: summing

Post by sonsjack »

Dank Hans; ik zie nu dat ik vergeten was dat in een arrayfunctie in plaats van de EN-functie vermenigvuldiging moet worden gebruikt.
Ik ben nog steeds dol op al dit soort dingen; oude liefde roest niet, maar kennis/routine wel ;–)

Jack.