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.

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

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.

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.

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


You can see a very good explanation at Debra Dalgleish's site:

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


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

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.

Sorry, but I have to correct you on this Franz, that is the second time <g.
That is not Debra's site, Debra maintains the excellent
http://www.contextures.com

--
HTH

Bob Phillips

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

"Franz Verga" wrote in message
...
Gert-Jan wrote:
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.


You can see a very good explanation at Debra Dalgleish's site:

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


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Bob Phillips wrote:
Sorry, but I have to correct you on this Franz, that is the second
time <g. That is not Debra's site, Debra maintains the excellent
http://www.contextures.com


Sorry Bob, I apologize to you (also for my bad English), but I was really
sure I had taken that link from Debra's site... :-(

Anyway that page is very interesting and useful to understand SUMPRODUCT
function and its possible uses...

Sorry again...


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

What have you got to be sorry about, your team made the World Cup Final, and
were brilliant to boot.

--
HTH

Bob Phillips

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

"Franz Verga" wrote in message
...

Sorry Bob, I apologize to you (also for my bad English), but I was really
sure I had taken that link from Debra's site... :-(

Anyway that page is very interesting and useful to understand SUMPRODUCT
function and its possible uses...

Sorry again...


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy