How to calculate: sumproduct / db??

[Gert-Jan]

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.

 

[Franz Verga]

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

 

[Bob Phillips]

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)+(D2200*E2:E200))

--
HTH

Bob Phillips

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

 

[Gert-Jan]

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

 

[Franz Verga]

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]

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)

 

[Bob Phillips]

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]

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

 

[Bob Phillips]

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)