SUM IF Return Max Value

TheMSsForum.com: The Microsoft Software Forum

Hello there,

after a day of starring at it I'm convinced I need a little help.

I was looking for a way to return the highest value (of Collumn B) to B3
that matches the criteria (name) stated in A3. The database (in another
sheet) consist of 11 different names (in Collumn A) which all have 5
different values in Collumn B. This makes 55 rows.

Thanks in advance!
Top

Re: SUM IF Return Max Value by Bob

Bob
Tue Apr 26 10:03:51 CDT 2005

I am sure I haven't got it as i DON'T UNDERSTAND YOUR QUESTION FULLY.

=MAX(if(SHEET2!A1:A55=A1,Sheet2!B1:B55)

which is an array formula so commit with Ctrl-Shift-Enter

If wrong, post some data and expected results.

--
HTH

Bob Phillips

"Jasper" <Jasper@discussions.microsoft.com> wrote in message
news:1647D143-119B-4A81-AFEC-DCA204906806@microsoft.com...
> Hello there,
>
> after a day of starring at it I'm convinced I need a little help.
>
> I was looking for a way to return the highest value (of Collumn B) to B3
> that matches the criteria (name) stated in A3. The database (in another
> sheet) consist of 11 different names (in Collumn A) which all have 5
> different values in Collumn B. This makes 55 rows.
>
> Thanks in advance!


Top

Re: SUM IF Return Max Value by Bernie

Bernie
Tue Apr 26 10:09:51 CDT 2005

Jasper,

Array enter (enter using Ctrl-Shift-Enter) this formula in cell B3

=MAX(('Other Sheet Name'!A1:A55=A3)*('Other Sheet Name'!B1:B55))

Of course, change 'Other Sheet Name' to the actual name of the other sheet.

HTH,
Bernie
MS Excel MVP


"Jasper" <Jasper@discussions.microsoft.com> wrote in message
news:1647D143-119B-4A81-AFEC-DCA204906806@microsoft.com...
> Hello there,
>
> after a day of starring at it I'm convinced I need a little help.
>
> I was looking for a way to return the highest value (of Collumn B) to B3
> that matches the criteria (name) stated in A3. The database (in another
> sheet) consist of 11 different names (in Collumn A) which all have 5
> different values in Collumn B. This makes 55 rows.
>
> Thanks in advance!


Top

Re: SUM IF Return Max Value by Bob

Bob
Tue Apr 26 10:14:58 CDT 2005

oops, missing bracket

=MAX(IF(Sheet2!A1:A55=A1,Sheet2!B1:B55))

--
HTH

Bob Phillips

"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:%23nMqMFnSFHA.688@TK2MSFTNGP10.phx.gbl...
> I am sure I haven't got it as i DON'T UNDERSTAND YOUR QUESTION FULLY.
>
> =MAX(if(SHEET2!A1:A55=A1,Sheet2!B1:B55)
>
> which is an array formula so commit with Ctrl-Shift-Enter
>
> If wrong, post some data and expected results.
>
> --
> HTH
>
> Bob Phillips
>
> "Jasper" <Jasper@discussions.microsoft.com> wrote in message
> news:1647D143-119B-4A81-AFEC-DCA204906806@microsoft.com...
> > Hello there,
> >
> > after a day of starring at it I'm convinced I need a little help.
> >
> > I was looking for a way to return the highest value (of Collumn B) to B3
> > that matches the criteria (name) stated in A3. The database (in another
> > sheet) consist of 11 different names (in Collumn A) which all have 5
> > different values in Collumn B. This makes 55 rows.
> >
> > Thanks in advance!
>
>


Top