Using SUMPRODUCT

peted878 wrote:
I've some problems to get the SUMPRODUCT function to work. I want to sum a column for those rows where two different conditions are met.
When I do it in excel it works with this formula:
=PRODUKTSUMMA(($B$1:$B$3008=$A11)*('$C$1:$C$3008=$A$10);$F$1:$F$3008)
PRODUKTSUMMA is SUMPPRODUCT in Swedish

The text about SUMPRODUCT in the numbers manual says nothing.

If you read carefully the description of the functions, you will understand that

What you wrote is in fact
=($B$1:$B$3008=$A11)*('$C$1:$C$3008=$A$10)*($F$1:$F$3008)

alas,

$B$1:$B$3008=$A11 means nothing
'$C$1:$C$3008=$A$10 means nothing too.
$F$1:$F$3008 is a range. It's an illegal item as a multiplicator.

May you explain what you assume that these three items mean ?

Yvan KOENIG (from FRANCE samedi 1 novembre 2008 15:20:27)

I made an error in my late message.

=SUMPRODUCT($B1:$B$3008,$C$1:$C$3008,$F$1:$F$3008)

would be perfectly correct.

In the passed formula, what is wrong is the first part:

($B$1:$B$3008=$A11)*($C$1:$C$3008=$A$10)
+it seems that there is a typo. Because the ref $A11 is the only one which is semi-absolute. I assumes that it was supposed to be $A$11.+

(a) as is, it is not a range, it try to be a number: the result of a product.
(b) the operands in this product are meaningles.
($B$1:$B$3008=$A11) is meaningless which try to compare a range of cells to a value stored in $A11
($C$1:$C$3008=$A$10) is meaningless which try to compare a range of cells to a value stored in $A$10

So, I repeat my question: may you explain what you are trying to achieve with this formula?

Yvan KOENIG (from FRANCE samedi 1 novembre 2008 20:45:57)

Numbers is not Excel.
I repeat that
($B$1:$B$3008=$A11) means nothing for Numbers.
Create an auxiliary column which I will assume to be column K.

In this column use the formula:
=IF(AND(B=$A$11,C=$A$10),B*C,"")

Now you will get the wanted result with the formula:
=SUM.PROD(F,K)
or if you dont want to apply it to the entire columns
=SUM.PROD($F1:$F$3008,$K1:$K3008)

Things are simple when we take care to read carefully the description of every function to learn what they are allowed to do and what they aren't 😉

Yvan KOENIG (from FRANCE dimanche 2 novembre 2008 11:24:42)

peted878 wrote:
Ok, I thought SUMPRODUCT was the same as in Excel.

There is really no reason to make some assumption, Apple is not a Xerox machine 😉

Where can I find instructions for the formulas?

Of course in the Help.

The help doesn't start because of some bug and the manual says almost nothing.

Try to trash:
<startupVolume>:Users:<yourAccount>:Library:Preferences:com.apple.help.plist
<startupVolume>:Users:<yourAccount>:Library:Preferences:com.apple.helpviewer.pli st

You may look in the PDF User Guide delivered with every copy of iWork.



Yvan KOENIG (from FRANCE dimanche 2 novembre 2008 16:20:36)

peted,

The two programs do indeed treat SUMPRODUCT identically, a far as I can tell. The formula: =SUMPRODUCT(A2:B4, C2:D4) has the same effect in both programs. Here are the results where the same data is treated with the same formula by both programs.



The Help guides in both programs describe the function nearly identically. I agree that some additional examples would be helpful.

Jerry

Hi Jerrold

Is ($B$1:$B$3008=$A11)*($C$1:$C$3008=$A10) meaningful in Excel ?

It's not in Numbers.

Yvan KOENIG (from FRANCE dimanche 2 novembre 2008 17:59:34)

Yvan,

Yes, apparently that expression does work in Excel. Here's a screen shot:



Your formula is in D: =($A$4:$A$13=$A$1)*($B$4:$B$13=$B$1)

OP's formula in C1.

Jerry

Thanks

It's interesting to learn from time to time 😉

This thread proves, if it was not clear for everybody, that it's is not serious to enter a new toll with old habits.

Several beings worked seriously to describe Numbers (or Pages, or Keynote).
The time used to read the PDF User Guide delivered with every iWork's copy isn't wasted.
It will spare us a lot of time if we do that carefully.

Yvan KOENIG (from FRANCE lundi 3 novembre 2008 14:46:38)

First let me start by saying I'm no Numbers expert and I wasn't involved in the programming of Excel or Numbers. I have however, encountered the same problem between the two applications. I think the fundamental difference between the two apps is that Excel treats a boolean return (e.g. $B$1:$B$3008=$A11) as a 1 or a 0. Numbers does not. So if you where to put in a formula in Excel that used boolean expressions for some of the parameters, they would evaluate to a 1 for true or a 0 for false. The rows that return a 0 for any argument would be negated when the multiplication is done. I have several spreadsheets in Excel that use this technique to allow for 'complex' logic to be used without having to do a bunch of nested if statements or use a bunch of extra columns. When I imported my Excel sheets into Numbers I get an error that says the sumproduct function can't accept a Boolean.

I have not yet found an easy work around, though I am still looking. Hope this helps.

I apologize but I must repeat:

=($B$1:$B$3008=$A11) is meaningless for Numbers !



In E1, the formula is:
=($B$1:$B$10=$A7)

_the result is wrong (some cells in the range are FALSE)_

In E2, the formula is:
=($C$1:$C$10=$A6)

the result is correct (some cells in the range are FALSE)

As you see it's perfectly meaningless.

I think the fundamental difference between the two apps is that Excel treats a boolean return (e.g. $B$1:$B$3008=$A11) as a 1 or a 0. Numbers does not.

Wrong.

in D3, the formula is =B*C
both are FALSE so it returns 0
in D4, the formula is =B*C
both are TRUE so it returns 1

We just get the blue triangle telling us that Numbers accepts to apply on booleans but that it is borderline.

To convert a boolean to a number, just multiply it by one.
=B*1

Yvan KOENIG (from FRANCE vendredi 7 novembre 2008 15:41:23)

To recover what you get in Excel you must replace it by

=($B$1:$B$12=$A11)*1

and replace

=($B$1:$B$3008=$A11)*($C$1:$C$3008=$A10)
by
=($B$1:$B$3008=$A11)*($C$1:$C$3008=$A10) *1

Yvan KOENIG (from FRANCE vendredi 7 novembre 2008 15:19:27)

Thanks for the clarification. I have tried to use what you have outlined here in the Sumproduct function but without success. Rather then trying to articulate all the different variations I have tried, it may be more efficient (and hopefully less frustrating for you) for me just to show you an example in Excel and ask how you would duplicate the results in Numbers.

Below you will see two simple tables in Excel. The first table summarizes the totals based on a date comparison between the two tables. As you can see, the totals in B1:B8 represent the sum of the amounts in table 2 (B2:D18) for those rows that are >= the corresponding date in A1:A8. The only parameter that changes between the B1:B8 cells is the first (A1:A8).

!http://homepage.mac.com/cfavret/images/Excel SumprodExample.png!

Can this be done using the Sumproduct Function in Numbers or do I need to use something else? This is the last hurdle I have to overcome before I can finally convert the last of my Excel workbooks over to Numbers. Any insight you can give me would be greatly appreciated.

Thanks.

In my first response, I wrote:

If you read carefully the description of the functions, you will understand that…

In Numbers, the syntax of SUMPRODUCT is not the one which you uses.



The ranges used as parameters must be separated by comma (or semi-colon) when you typed an asterisk.

As I already work, there is no reason forcing Numbers to behave like Excel.

Before writing a formula, it's often useful to check in the Help the correct syntax and most of the time, the Insert Functions tool insert the correct syntax. I'm sure that it's not it which inserted the wrong asterisk 😉

Yvan KOENIG (from FRANCE mardi 11 novembre 2008 15:26:11)