max with double criteria in a range

[pinstripe]

Hi,

right now i have this:

A- AC- C
5 --1--10
2---1--15
4---0--4
8---1---17
5---1---13
5---0---21

=MAX(IF((AC$2:AC$413=1);(AB$2:AB$413)))
this is ok it gives me back the max in C (17) although, when pulling down i need to refraze this formula so it will look in a range when pulling down like this:

A- AC- C.......X
5 --1--10 .....13
2---1--15......15
4---0--4........0
8---1---17.....17
5---1---13.....13
5---1---21......0

How can i remake thi formula?
thank you

[NBVC]

I don't understand what you mean by

when pulling down i need to refraze this formula so it will look in a range when pulling down like this:

please be more detailed....

Are you trying to fill in column X? How do you get those numbers?

[pinstripe]

I don't understand what you mean by

please be more detailed....

Are you trying to fill in column X? How do you get those numbers?

hi,
yes, i would like to fill column X with pulling down the formula.
A- AC- C.......X
5 --1--10 .....13 ---- this is max in C when meeting criteria A=5 and AC=1
2---1--15......15---- this is max in C when meeting criteria A=2 and AC=1
4---0--4........0---- this is max in C when meeting criteria A=5 and AC=0
8---1---17.....17 if it is AC 0 then te max should be 0
5---1---13.....13
5---0---21......0

is this explained better?
thank you

[NBVC]

Try (using the column indexes you've indicated, A, AC and C):

=IF(AC2=0,0,MAX(IF($A$2:$A$413=A2,IF($AC$2:$AC$413=AC2,$C$2:$C$413))))


which must be confirmed with CTRL+SHIFT+ENTER not just ENTER and copied down.

[pinstripe]

god bless you ...this is almost working. It does the major job already, just the thing is the when i pull down the formula i get max in every cell in X where criteria meet...A and AC
formost i have explained it wrong. In X should be: If A=5 and AC=1 then X= max for that criteria.

A- AC- C.......X
5 --1--10 .....10 --beacuse it is not max for criteria
2---1--15......15-- max
4---0--4........4-- beacuse it is AC=0
8---1---17.....17--beacuse it is max
5---1---13.....13--beacuse it is max
5---0---21......21--beacuse it is AC=0

I am sorry, i guess i wasn't thinking right.
Can this be accomplished?

thank you

[NBVC]

I don't understand....

so is my formula giving incorrect results? or are you changing your request?

Look at the attached sample sheet....

Is it wrong results in column X?

[pinstripe]

to be more specific what i am trying to do:


deduct sum of A =5 and AC = 0 just from the max with criteria A=5 and AC=1!
let say: sumC (A =5 and AC = 0) - maxC( A=5 and AC=1)
so we get 21+4 =25 .....13- (25) = -12
this is exemple but all should be treated this way.
A- AC- C.......X
5 --1--10 .....10 --
2---1--15......15--
4---0--4........4--
8---1---17.....17--
5---1---13.....12-----the result
5---0---21......21--
5---0---4.......4

thanks for bering with me