maxif

[RobFJ]

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

 

[RobFJ]

Should have added I'm using XL2007

 

[muddan madhu]

try this

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

 

[Don Guillett]

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

 

[Ashish Mathur]

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

 

[Chip Pearson]

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)