SUMIF with Functions?

asked 2012-07-02 07:02:25 +0200

DV88
33 ● 2 ● 3 ● 8

updated 2012-07-02 07:04:04 +0200

Hi, I'm not sure this is even possible, but I'm hoping someone can show me how if it is (and that it is). The best way I can think to describe this is to make a little diagram, so I hope this is readable.

   A  B  C
1  5  1  A
2  7  3  B
3  2  2  A
4  9  4  C

Ok, what I want to do is make 1 cell (let's say D5, just for kicks) give me the sum of column A where column C is "A" (so 7, 5 from row 1 and 2 from row 3) and the max of column B where column C is "A" (so 2, for row 3, since row 3's 2 is higher than row 1's 1), for, in this case, a total of 9.

I tried =SUMIF(C1:C4,"A",A1:A4+MAX(B1:B4)), but that didn't work. Nor did =SUMIF(C1:C4,"A",A1:A4)+SUMIF(C1:C4,"A",MAX(B1:B4)). Both of those give Error code 504 (SUMIF doesn't like any other functions apparently). I also tried =SUMIF(C1:C4,"A",A1:A4)+IF(C1:C4,"A",MAX(B1:B4)), which doesn't give an error but gives the max of column B regardless of whether its corresponding cell in column C is "A" or not.

Is there any way to do this using only the single cell for the total (as opposed to making separate formulae for the sum of A and the max of B and then referencing those with a third formula)?