CODEWITHSUNDEEP
excel
user1778727
user1778727

Reputation: 13

Sum max values in a column based on two other columns

I have three columns and I want the sum of the maximum values in Col2 for each category in Col1 where Col3 is equal to x.

I am not able to add a 4th column to obtain the max first.

Col1    Col2    Col3  
a       3       x  
b       2       x  
c       2       x  
a       1       x  
b       3       x  
c       1       y  
a       2       y  
b       1       y  
c       3       y

In this example the answer I am looking for is 8:

How can I do this?

Upvotes: 1

Views: 3243

Answers (1)

lori_m
lori_m

Reputation: 5567

You could try this with CTRL+SHIFT+ENTER with data in A2:C10 and D1="x":

=SUM(IF(C2:C10=D1,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10,C2:C10,D1)=0,B2:B10)))

but note that if there might be more than one max value for a category this sums multiple values. To sum unique max values per category you could try this alternative (also with CSE):

=SUM(IF(C2:C10=D1,(MATCH(A2:A10,IF(COUNTIFS(A2:A10,A2:A10,B2:B10,">"&B2:B10,C2:C10,D1)=0,A2:A10),0)=ROW(A2:A10)-MIN(ROW(A2:A10))+1)*B2:B10))

For example changing the first value from 3 to 1 gives 7 in the first formula and 6 in the second.

Upvotes: 2

Related Questions