I want to report the maximum number in column A, providing a condition in
column B is satisfied. All data in rows A and B are integers..

eg Max for 37 in the following table would be 65

Col A Col B

23 37
122 32
65 37


Can SKS help with the syntax

TIA

Rob

Should have added I'm using XL2007


"RobFJ" <del**eteto**(E-Mail Removed)> wrote in message
news:4pCPk.13740$_(E-Mail Removed)2...
>I want to report the maximum number in column A, providing a condition in
>column B is satisfied. All data in rows A and B are integers..
>
> eg Max for 37 in the following table would be 65
>
> Col A Col B
>
> 23 37
> 122 32
> 65 37
>
>
> Can SKS help with the syntax
>
> TIA
>
> Rob
>
>
>

try this

=MAX(IF(B1:B20=37,A1:A20)) ( use ctrl + shift + enter )



On Nov 3, 5:46*pm, "RobFJ" <del**eteto**rfj1...@ntlworld.com> wrote:
> I want to report the maximum number in column A, providing a condition in
> column B is satisfied. All data in rows A and B are integers..
>
> eg Max for 37 in the following table would be 65
>
> Col A * *Col B
>
> 23 * * * * * *37
> 122 * * * * *32
> 65 * * * * * *37
>
> Can SKS help with the syntax
>
> TIA
>
> Rob

This is an array formula that must be entered using ctrl+shift+enter
=MAX(IF(J2:J22=37,I2:I22))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"RobFJ" <del**eteto**(E-Mail Removed)> wrote in message
news:4pCPk.13740$_(E-Mail Removed)2...
>I want to report the maximum number in column A, providing a condition in
>column B is satisfied. All data in rows A and B are integers..
>
> eg Max for 37 in the following table would be 65
>
> Col A Col B
>
> 23 37
> 122 32
> 65 37
>
>
> Can SKS help with the syntax
>
> TIA
>
> Rob
>
>

Hi,

Try this =max(if((rangeB=37),rangeA))

Alternatively, you may also read up on the DMAX() formula in Excel's Help
menu

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"RobFJ" <del**eteto**(E-Mail Removed)> wrote in message
news:4pCPk.13740$_(E-Mail Removed)2...
> I want to report the maximum number in column A, providing a condition in
> column B is satisfied. All data in rows A and B are integers..
>
> eg Max for 37 in the following table would be 65
>
> Col A Col B
>
> 23 37
> 122 32
> 65 37
>
>
> Can SKS help with the syntax
>
> TIA
>
> Rob
>
>

You can use a formula like the following array formula.

=MAX(IF(B1:B7="a",A1:A7,MIN(A1:A7)))

This will return the maximum value from A1:A7 where the corresponding
value in B1:B7 is an "a". Since this is an array formula, you *must*
press CTRL SHIFT ENTER rather than just ENTER when you first enter the
formula and whenever you edit later. If you do this properly, Excel
will display the formula enclosed in curly braces { }. For lots more
information about array formulas, see
http://www.cpearson.com/Excel/ArrayFormulas.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 3 Nov 2008 12:46:53 -0000, "RobFJ"
<del**eteto**(E-Mail Removed)> wrote:

>I want to report the maximum number in column A, providing a condition in
>column B is satisfied. All data in rows A and B are integers..
>
>eg Max for 37 in the following table would be 65
>
>Col A Col B
>
>23 37
>122 32
>65 37
>
>
>Can SKS help with the syntax
>
>TIA
>
>Rob
>