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" wrote in message
...
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" 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

"RobFJ" wrote in message
...
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" wrote in message
...
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"
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