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.
summing
-
- Administrator
- Posts: 78237
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: summing
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))
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
Hans
-
- NewLounger
- Posts: 22
- Joined: 02 Jun 2011, 21:46
Re: summing
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.
Ik ben nog steeds dol op al dit soort dingen; oude liefde roest niet, maar kennis/routine wel ;–)
Jack.