Re: How to calculate: sumproduct / db??

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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" <gjvanmiddelkoop@xxxxxxxxxx> wrote in message
news:44abc1a7$0$23511$9ba1ec04@xxxxxxxxxxxxx
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" <bob.NGs@xxxxxxxxxxxxx> schreef in bericht
news:%23xpE6sBoGHA.956@xxxxxxxxxxxxxxxxxxxxxxx
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:E200))

--
HTH

Bob Phillips

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

"Gert-Jan" <gjvanmiddelkoop@xxxxxxxxxx> wrote in message
news:44ab8569$0$22557$9ba1ec04@xxxxxxxxxxxxx
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.








.

Relevant Pages

  • Re: array formula(s)- not working, need some help please (problem with an AND)
    ... returns an array of TRUE/FALSE values. ... single unary, -, transforms the TRUE/FALSE to 1/0, but a negative 1. ... I got as far as this first array formula, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Multiple ComboBoxes, one data source
    ... have to make sure that each combobox uses a separate BindingContext. ... You could also get around this by copying the contents of the array into ... Fruit 2: ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Multiple ComboBoxes, one data source
    ... You could also get around this by copying the contents of the array into ... Fruit 1: ... I have set the datasource on all the comboBoxes to be fruitList ... I want to make fruit 1 an apple, fruit 2 a bannana and fruit 3 a pear, ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Enumerations
    ... > So, way back when I was first learning how to program, I think I used Pascal ... enum fruit {APPLE, BANANA, CARROT, NUM_FRUITS}; ... But add a name (and carrots aren't a fruit:) ... but you got rid of my array. ...
    (comp.lang.lisp)
  • Re: Trouble accessing parsed XML variable
    ... $x above isn't a hash reference, note the square brackets in your ... Dumper output - $x is an array reference. ...
    (perl.beginners)