Ask Your Question
1

SUMIF with Functions? [closed]

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

DV88 gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-17 00:28:51.515751

1 Answer

Sort by » oldest newest most voted
2

answered 2012-07-06 08:32:52 +0200

JohnSUN gravatar image

Try the "array formula":

{=SUMIF(C1:C4;"A";A1:A4)+MAX((B1:B4)*(C1:C4="A"))}

(End the entry the formula by pressing the Ctrl+Shift+Enter)

edit flag offensive delete link more

Question Tools

1 follower

Stats

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

Seen: 10,646 times

Last updated: Jul 06 '12