Hello All,

I need a Max statement with multiple ifs.

Take for instance the example below, I need only the Max IF A=1 and C
= 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF
(Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF
(Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far.

So basically, for all values of A which are 1, I need the Max of B, if
C is also 1, then for all values of A which are 2, I need the Max of B
if C is 1, etc.

Thanks,

Chris

Example:

A B C

1 5 1

1 3 1

1 2 0

2 3 1

2 6 0

3 2 1

3 5 0

3 3 1

So what I'm looking for here is for when A=1 the cell will equal 5,
when A=2 the cell should say 2, and when A=3, the cell should say 3.

On Jul 21, 3:47*pm, ChrisWWiese wrote:
*Hello All,

I need a Max statement with multiple ifs.

Take for instance the example below, I need only the Max IF A=1 and C
= 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF
(Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF
(Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far.

So basically, for all values of A which are 1, I need the Max of B, if
C is also 1, then for all values of A which are 2, I need the Max of B
if C is 1, etc.

Thanks,

Chris

Example:

A * * * * B * * * * C

1 * * * * 5 * * * * *1

1 * * * * 3 * * * * *1

1 * * * * 2 * * * * *0

2 * * * * 3 * * * * *1

2 * * * * 6 * * * * *0

3 * * * * 2 * * * * *1

3 * * * * 5 * * * * *0

3 * * * * 3 * * * * *1

On Tue, 21 Jul 2009 12:51:27 -0700 (PDT), ChrisWWiese
wrote:

So what I'm looking for here is for when A=1 the cell will equal 5,
when A=2 the cell should say 2, and when A=3, the cell should say 3.

On Jul 21, 3:47*pm, ChrisWWiese wrote:
*Hello All,

I need a Max statement with multiple ifs.

Take for instance the example below, I need only the Max IF A=1 and C
= 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF
(Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF
(Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far.

So basically, for all values of A which are 1, I need the Max of B, if
C is also 1, then for all values of A which are 2, I need the Max of B
if C is 1, etc.

Thanks,

Chris

Example:

A * * * * B * * * * C

1 * * * * 5 * * * * *1

1 * * * * 3 * * * * *1

1 * * * * 2 * * * * *0

2 * * * * 3 * * * * *1

2 * * * * 6 * * * * *0

3 * * * * 2 * * * * *1

3 * * * * 5 * * * * *0

3 * * * * 3 * * * * *1


Try the following formula:

=MAX((B$1:B$100)*(A$1:A$100=x)*(C$1:C$100=1))

Note: This is an array formula that has to be cofirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Replace the 100 on three places to suit the number of data rows in
column A,B, and C.

Change the "x" to 1, 2, or 3 to get the different results you expect
(or replace "x" with a cell reference, e.g. D1 where you put the
number.

Hope this helps / Lars-Åke

On Jul 21, 3:59*pm, Lars-Åke Aspelin
wrote:
On Tue, 21 Jul 2009 12:51:27 -0700 (PDT), ChrisWWiese





wrote:
So what I'm looking for here is for when A=1 the cell will equal 5,
when A=2 the cell should say 2, and when A=3, the cell should say 3.

On Jul 21, 3:47*pm, ChrisWWiese wrote:
*Hello All,

I need a Max statement with multiple ifs.

Take for instance the example below, I need only the Max IF A=1 and C
= 1. I've tried formulas like {=MAX(IF(Sheet1!A:A=A2, Sheet1!C:C, IF
(Sheet1!E:E="yes", Sheet1!C:C)))} and {=IF(Sheet1!A:A=78, MAX(IF
(Sheet1!E:E="yes",Sheet1!C:C)))}, but I've had no luck so far.

So basically, for all values of A which are 1, I need the Max of B, if
C is also 1, then for all values of A which are 2, I need the Max of B
if C is 1, etc.

Thanks,

Chris

Example:

A * * * * B * * * * C

1 * * * * 5 * * * * *1

1 * * * * 3 * * * * *1

1 * * * * 2 * * * * *0

2 * * * * 3 * * * * *1

2 * * * * 6 * * * * *0

3 * * * * 2 * * * * *1

3 * * * * 5 * * * * *0

3 * * * * 3 * * * * *1

Try the following formula:

=MAX((B$1:B$100)*(A$1:A$100=x)*(C$1:C$100=1))

Note: This is an array formula that has to be cofirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Replace the 100 on three places to suit the number of data rows in
column A,B, and C.

Change the "x" to 1, 2, or 3 to get the different results you expect
(or replace "x" with a cell reference, e.g. D1 where you put the
number.

Hope this helps / Lars-Åke- Hide quoted text -

- Show quoted text -

That works! Thank you!