Aggegate formula

[mbsmeltzer]

Aggegate formula not giving sum of lowest 5 cells when there are more than 5 cells that are equal. How do I make G2 equal the lowest 5 cells from column D when all from that team have the same score? G2 should equal 4.5


help_file.xlsx

[AlKey]

Use this formula to get SUM of 5 lowest cells

=SUM(SMALL(D2:D14,{1,2,3,4,5}))

[mbsmeltzer]

This solution continues with the same problem. Team 1 still has a score of 6.5 when the score should be 4.5. Thank you for trying!

[AlKey]

The formula I gave you returns 4.5

[mbsmeltzer]

I have it in like this: =SUMIFS($D$2:$D$500,$A$2:$A$500,E3,$D$2:$D$500,"<="&SUM(SMALL(D3:D15,{1,2,3,4,5})))

and I get 6.5. I can't run it without the SUMIFS because I need it to be thinking about the individual teams.

[FlameRetired]

Try this in G2:G3

Please Login or Register  to view this content.

Row\Col	A	B	C	D	E	F	G
1	teamname	divname	runaplace	adjusted score	teamname	Team Count	Score
2	Team 1	Half Marathon	1	1	Team 1	7	4.5
3	Team 1	Half Marathon	1	1	Team 2	6	16
4	Team 1	Half Marathon	1	1
5	Team 1	Half Marathon	1	1
6	Team 1	Half Marathon	1	1
7	Team 1	Half Marathon	1	1
8	Team 1	Full Marathon	1	0.5
9	Team 2	Full Marathon	3	1.5
10	Team 2	Half Marathon	3	3
11	Team 2	Full Marathon	7	3.5
12	Team 2	Half Marathon	4	4
13	Team 2	Half Marathon	4	4
14	Team 2	Half Marathon	5	5

Does this return what you expect?

[MarvinP]

Hi,

This looks like a CSE answer to me. Something like:

=SUM(SMALL(IF($A$2:$A$14=E2,$D$2:$D$14,""),{1,2,3,4,5})) (confirm with CSE)

See attached.

[mbsmeltzer]

Thank you Dave! This one did the trick! =SUM(AGGREGATE(15,6,$D$2:$D$500/($A$2:$A$500=E2),{1,2,3,4,5}))

[FlameRetired]

You are welcome. Glad you found your solution, and thanks for the rep.