Gert-Jan wrote:
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.


Hi Gert-Jan,

If, as it should be, price of one box (column C) = number of pieces fruit in
a box (column D) * price per piece of the fruit (column E), you can use one
of these SUMPRODUCT formulas:

=SUMPRODUCT(($A$2:$A$20=$G$1)*($B$2:$B$20)*($D$2:$ D$20)*($E$2:$E$20))

or

=SUMPRODUCT(($A$2:$A$20=$G$1)*($B$2:$B$20)*($C$2:$ C$20))


Where in G1 you can type (or maybe you can use Validation Data...) the name
of the fruit.

You have to adjust the references of ranges as for your conveniences, but be
aware that you can't use the whole column as argument for SUMPRODUCT.


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy