# maxif

RobFJ
Guest
Posts: n/a

 3rd Nov 2008
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
Guest
Posts: n/a

 3rd Nov 2008
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
>
>
>

Guest
Posts: n/a

 3rd Nov 2008
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

Don Guillett
Guest
Posts: n/a

 3rd Nov 2008
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
>
>

Ashish Mathur
Guest
Posts: n/a

 3rd Nov 2008
Hi,

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

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

--
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
>
>

Chip Pearson
Guest
Posts: n/a

 3rd Nov 2008
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
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
>

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?SkFL?= Microsoft Excel Programming 7 22nd Feb 2005 07:11 AM =?Utf-8?B?U3RldmVu?= Microsoft Excel Misc 3 9th Feb 2005 03:31 AM mienz Microsoft Excel Programming 5 10th Nov 2004 06:23 PM dbranca@ctcfl.com Microsoft Excel Programming 4 29th Mar 2004 08:30 PM Ann Scharpf Microsoft Excel Worksheet Functions 1 22nd Oct 2003 05:03 PM

Features