CODEWITHSUNDEEP
excelexcel-formulamaxsumifs
Olivebel
Olivebel

Reputation: 1

Sum the maximum values based on criteria Excel

The objective is to calculate the total allowance for the kids in my data set that are aged 12+. In this example using the data set below, the outcome should be 9 (5+2+2). Can someone please help me with a formula? Thanks!

ID      Age   Allowance
Fred    12    6
Fred    15    10
Fred    18    5
Joe     12    2
Sam     12    2
DJ      10    1

Upvotes: 0

Views: 56

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

With Office 365 Excel:

=SUMPRODUCT(SUMIFS(C:C,A:A,A2:A7,B:B,">=12",B:B,MAXIFS(B:B,A:A,A2:A7))/COUNTIFS(A:A,A2:A7))

enter image description here

If one does not have Office 365 Excel then one can use a helper column and sum that.

In D2 Put:

=IF(AND(B2=AGGREGATE(14,7,$B$2:$B$7/($A$2:$A$7=A2),1),B2>=12),C2,"")

And copy down. Then sum at the bottom.

enter image description here

Upvotes: 1

Related Questions