excel 2003 can Max function be in sumproduct?

[hrm1220]

I have the following formula which works.
But now I'm being asked to only provide the max value and not the sum, based on the 2 criteria.

SUMPRODUCT(--(('[test.xls]Sheet1'!$AA$1:$AA$10000=$C13)*('[test.xls]Sheet1'!$W$1:$W$10000=K$1)),'[test.xls]Sheet1'!$V$1:$V$10000)

So is there a way to add the MAX Function into a sumproduct formula, or am I making this more complicated then need be?

thx for any help

 

[SkipVought]

Hi,

Not really clear HOW you want the MAX to work, but here's a guess...
[tt]
SUMPRODUCT(--(('[test.xls]Sheet1'!$AA$1:$AA$10000=MAX('[test.xls]Sheet1'!$AA$1:$AA$10000))*('[test.xls]Sheet1'!$W$1:$W$10000=K$1)),'[test.xls]Sheet1'!$V$1:$V$10000)[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hrm1220]

my apologies for not being clear. I was looking for the max value based on the 2 criteria given in the 1st section of the sumproduct, so I was trying something like:

SUMPRODUCT(--(('[test.xls]Sheet1'!$AA$1:$AA$10000=$C13)*('[test.xls]Sheet1'!$W$1:$W$10000=K$1)),max('[test.xls]Sheet1'!$V$1:$V$10000))

 

[Gavona]

Your example would help us to suggest a resolution but I think the answer is "No". I see no reason why the DMAX function should not do the job for you.

Gavin

 

[GlennUK]

Yes, you can, simply:

=SUMPRODUCT(MAX(--(('[test.xls]Sheet1'!$AA$1:$AA$10000=$C13)*('[test.xls]Sheet1'!$W$1:$W$10000=K$1))*'[test.xls]Sheet1'!$V$1:$V$10000))

Cheers, Glenn.

Beauty is in the eye of the beerholder.