sumproduct?

[lars]

Here is my formula
=SUMPRODUCT(('327'!B6:B424=Summary!A10)*(IF(SUM('327'!R6:R424)>SUM('327'!S6:S424),'327'!R6:R424,'327'!S6:S424)))


The formula looks at a voyage (summary!A10)
and finds that in the B6:B424 range and then goes over in the table and looks at two sets of numbers one in the range R6:R424 and another in range S6:S424 whichever one is greater should come back. but for some reason it gives me the smaller of the two.

Any suggestions?

Thanks

[Ian Mac]

On 2002-05-23 15:37, lars wrote:
Here is my formula
=SUMPRODUCT(('327'!B6:B424=Summary!A10)*(IF(SUM('327'!R6:R424)>SUM('327'!S6:S424),'327'!R6:R424,'327'!S6:S424)))


The formula looks at a voyage (summary!A10)
and finds that in the B6:B424 range and then goes over in the table and looks at two sets of numbers one in the range R6:R424 and another in range S6:S424 whichever one is greater should come back. but for some reason it gives me the smaller of the two.

Any suggestions?

Thanks

I've tries to replicate your error and not found any problems, the following is a SIMPLE set-up to what I believe you have:

Microsoft Excel - 12.xls ___Running: xl2002 XP : OS = Windows (32-bit) 4.10
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp
E1 = m
	A	B	C	D	E
1	m	1	2		m
2	9	1	2
3	9	1	2
4	9	1	2		:alert('=SUMPRODUCT((A1:A20=E1)*(IF(SUM(B1:B20)%3ESUM(C1:C20),B1:B20,C1:C20)))')>14
5	9	1	2
6	m	1	2
7	9	1	2
8	9	1	2
9	9	1	2
10	9	1	2
11	m	1	2
12	9	1	2
13	9	1	2
14	9	1	2
15	9	1	2
16	9	1	2
17	m	1	2
18	m	1	2
19	m	1	2
20	m	1	2
Sheet1

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

The above image was automatically generated by [HtmlMaker V1.27]
If you want FREE SOFT, 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


Can you extend on what the fields contain that your looking at?

[Dave Patton]

Do you want the Max of the 2 Totals or the Max of the sum that meets the criteria in A10?

Your formula should give the maximum Total.

For Max of numbers that meet criteria, consider

=MAX(SUMPRODUCT(('327'!B6:B10=A10)*('327'!R6:R10)),SUMPRODUCT(('327'!B6:B10=A10)*('327'!S6:S10)))

Edit the ranges to fit your information.

[Aladin Akyurek]

On 2002-05-23 15:37, lars wrote:
Here is my formula
=SUMPRODUCT(('327'!B6:B424=Summary!A10)*(IF(SUM('327'!R6:R424)>SUM('327'!S6:S424),'327'!R6:R424,'327'!S6:S424)))


The formula looks at a voyage (summary!A10)
and finds that in the B6:B424 range and then goes over in the table and looks at two sets of numbers one in the range R6:R424 and another in range S6:S424 whichever one is greater should come back. but for some reason it gives me the smaller of the two.

Any suggestions?

Thanks

Maybe you're looking for:

=SUMPRODUCT(('327'!B6:B424=Summary!A10)*(MAX('327'!R6:R424,'327'!S6:S424)))

[lars]

Thanks Dave and Aladin, I used Daves because Aladins gave me the total of both columns and Daves gave me the greater of the two which is what I needed.

Thanks guys

[Aladin Akyurek]

On 2002-05-24 09:34, lars wrote:
Thanks Dave and Aladin, I used Daves because Aladins gave me the total of both columns and Daves gave me the greater of the two which is what I needed.

Thanks guys

Lars,

In that case, I'd strongly suggest (I reckon Dave also would agree)switching to SUMIF:

=MAX(SUMIF('327'!B6:B10,A10,'327'!R6:R10),SUMIF('327'!B6:B10,A10,'327'!S6:S10))

Aladin

[lars]

Aladin, i like the suggestion it is easier to work with.

Thanks