Hi Gert-Jan,

simply put, the test

(A2:A200="Apple")

returns an array of TRUE/FALSE values. Performing an arithmetic operation on
it transforms this to an array of 1/0 values, which SP can use to multiply
by the actual values to get only the values where a condition is met. So a
single unary, -, transforms the TRUE/FALSE to 1/0, but a negative 1. So a
second is required to get it back to positive 1.

Thee is a lot more detail at

http://www.xldynamic.com/source/xld.SUMPRODUCT.html .

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gert-Jan" wrote in message
...
Hi Bob, this works great, thanks a lot!

I have done a lot with formulas, but I have never seen that -- in a
formula
before. Removing one makes the ammount negative, removing them both makes
the result zero.

Can you shortly explain how this works and when to use it?

Gert-Jan

"Bob Phillips" schreef in bericht
...
I don't understand why you multiply the number of boxes by the price per
box
and the number of pieces per box * price per piece, but this does it

=SUMPRODUCT(--(A2:A200="Apple"),(B2:B200*C2:C200)+(D2:D200*E2:E2 00))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Gert-Jan" wrote in message
...
In a database I have:

column A: name of fruit
column B: number of boxes
column C: price of one box
column D: number of pieces fruit in a box
column E: price per piece of the fruit

I would like to know how to calculate the total value of one sort of
fruit.
So, the result must be for "apple" the sum of (columnB *
columnC)+(columnD
*
columnE). Should I use the db-functions / sumproduct? Any help /
suggestion
would be highly appriciated.