sumproducts explained (if you have enough memory)

[AmberJo]

What I want to do is a 'sumif function' with 2 criteria-

ie if member is class 2 and decile 1.

Is there a way of linking these two criteria together in this type of function?

[ This Message was edited by: AmberJo on 2002-05-12 22:44 ]

[Aladin Akyurek]

On 2002-05-12 17:59, AmberJo wrote:
What I want to do is a 'sumif function' with 2 criteria-

ie if member is class 2 and decile 1.

Is there a way of linking these two criteria together in this type of function?

No.

You need a SUMPRODUCT, an array, or DSUM formula:

=SUMPRODUCT((Range1=E1)*(Range2=E2),Range3)

where Range1 and Range2 are ranges for which the criteria/conditions in E1 and E2 (such as class and decile values), repectively, must hold. Range3 is the range to be summed.

[Qroozn]

sorry,
I didnt realise what class and decile was referring to!

[AmberJo]

I can't get it to work:
Column F is decile, Column B is class, and column K is total savings (the bit I want summed).

I want the total savings of members who are in decile 1 AND class 2. This is what I've got so far :

=SUMPRODUCT('[May 2 02 without formulas.xls]Sheet1'!$F:$F=1)*('[May 2 02 without formulas.xls]Sheet1'!$B:$B=2), ('[May 2 02 without formulas.xls]Sheet1'!K:K)

Am I on the right track?

[Aladin Akyurek]

On 2002-05-12 19:14, AmberJo wrote:
I can't get it to work:
Column F is decile, Column B is class, and column K is total savings (the bit I want summed).

I want the total savings of members who are in decile 1 AND class 2. This is what I've got so far :

=SUMPRODUCT('[May 2 02 without formulas.xls]Sheet1'!$F:$F=1)*('[May 2 02 without formulas.xls]Sheet1'!$B:$B=2), ('[May 2 02 without formulas.xls]Sheet1'!K:K)

Am I on the right track?

Yes. Just substitute real, definite ranges for $F:$F, $B:$B, and $K:$K. Such column args are not accepted in SUMPRODUCT or array-formulas.

[AmberJo]

XL still doesn't like it. It suggests I put a * instead of a comma before the last bit (ie =SUMPRODUCT('[May 2 02 without formulas.xls]Sheet1'!F1:F13000=1)*('[May 2 02 without formulas.xls]Sheet1'!B1:B13000=2)*('[May 2 02 without formulas.xls]Sheet1'!K1:K13000)

When I do this it comes up with an #N/A thingy. Any ideas?

[Aladin Akyurek]

On 2002-05-12 19:28, AmberJo wrote:
XL still doesn't like it. It suggests I put a * instead of a comma before the last bit (ie =SUMPRODUCT('[May 2 02 without formulas.xls]Sheet1'!F1:F13000=1)*('[May 2 02 without formulas.xls]Sheet1'!B1:B13000=2)*('[May 2 02 without formulas.xls]Sheet1'!K1:K13000)


When I do this it comes up with an #N/A thingy. Any ideas?

=SUMPRODUCT(('[May 2 02 without formulas.xls]Sheet1'!F1:F13000=1)*('[May 2 02 without formulas.xls]Sheet1'!B1:B13000=2),('[May 2 02 without formulas.xls]Sheet1'!K1:K13000))

should be Ok.

BTW: a "*" as well as a "," will both work between the last 2 arguments.


[ This Message was edited by: Aladin Akyurek on 2002-05-12 19:30 ]

[ This Message was edited by: Aladin Akyurek on 2002-05-12 19:32 ]

[Yogi Anand]

On 2002-05-12 19:28, AmberJo wrote:
XL still doesn't like it. It suggests I put a * instead of a comma before the last bit (ie =SUMPRODUCT('[May 2 02 without formulas.xls]Sheet1'!F1:F13000=1)*('[May 2 02 without formulas.xls]Sheet1'!B1:B13000=2)*('[May 2 02 without formulas.xls]Sheet1'!K1:K13000)

When I do this it comes up with an #N/A thingy. Any ideas?

Hi AmberJo:
The problem might be with syntax (the organization of the formula), and not with the logic of the formula that Aladin gave you.
Look at you formula carefully, and see if you have supplied everything correctly as Aladin suggested. You may also post the actual formula that is giving you the #N/A -- please also post the values of the cells in your formula.

Regards!

[Aladin Akyurek]

See the figure, in which I also added the use of DSUM. Since you have a big range, the latter might suit you better.

Microsoft Excel - May 2 02 without formulas.xls_______________Running: xl2000 : OS = Windows (32-bit) NT 5.00

(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp

B1

=

labelB

*

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

1

labelB

*

*

*

labelF

*

*

*

*

labelK

*

*

*

labelB

labelF

2

1

*

*

*

2

*

*

*

*

40

*

*

*

1

2

3

2

*

*

*

1

*

*

*

*

20

*

*

*

*

*

4

1

*

*

*

4

*

*

*

*

30

*

:alert('=SUMPRODUCT(($B$2:$B$6=1)*($F$2:$F$6=2),($K$2:$K$6))')>90

*

:alert('=DSUM(B1:K6,"labelK",O1:P2)')>90

*

5

3

*

*

*

2

*

*

*

*

25

*

*

*

*

*

6

1

*

*

*

2

*

*

*

*

50

*

*

*

*

*

Sheet1

To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.20]
If you want this code, click here and Colo will email the file to you.
This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo




[ This Message was edited by: Aladin Akyurek on 2002-05-12 19:59 ]