Google Sheets - Formula Help!

[daithi_ie]

Hi Folks,

Is there a formula I can use to add the total time for the team below based on the three lowest penalty scores, the highest penalty score is a discard score.

Based on the results below we'd need to add John, Paul and Ray's penalties and times and I have a formula to do this for the three lowest penalty scores. This won't work for the times though as the times are not the three lowest of the four as the times that need to be added as the ones are next to the lowest penalties. Is there a formula to add the times automatically based on the lowest penalties?

I hope I've explained this correctly,

Any help much appreciated

Competitor	Penalties	Time
John	0	56.78
Tony	8	52.31
Paul	4	60.23
Ray	1	63.23

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	Competitor	Penalties	Time
2	John	0	56.78		180.24
3	Tony	8	52.31
4	Paul	4	60.23
5	Ray	1	63.23
Main

Cell Formulas
Range	Formula
E2	E2	=SUMIFS(C2:C100,B2:B100,"<="&SMALL(B2:B100,3))

 

[daithi_ie]

Thank you so much - it works perfectly!

You're a lifesaver

 

[Fluff]

Glad to help & thanks for the feedback.

 

[daithi_ie]

So I've been playing round with the formula and where there is equality of penalties, the formula counts all four scores. Is there anyway around this perhaps?

See the example below - If I use the formula it gives a time of 272.68 instead of 205.67

Penalties Time Total Time

0	70.32		205.67
0	66.59
4	67.01
4	68.76

 

[Fluff]

How would the formula know which of the two 4 penalties scores to count?

 

[daithi_ie]

It would be the quickest time - is there a work around for that perhaps?

Thanks

 

[Fluff]

Ok, how about with a helper column

Fluff.xlsm
	A	B	C	D	E
1	Competitor	Penalties	Time
2	John	0	70.32	1	205.67
3	Tony	0	66.59	2
4	Paul	4	67.01	4
5	Ray	4	68.76	3
Main

Cell Formulas
Range	Formula
E2	E2	=SUMIFS(C2:C100,D2:D100,"<=3")
D2:D5	D2	=RANK(B2,$B$2:$B$5,1)+COUNTIFS($B$2:$B$5,B2,$C$2:$C$5,">"&C2)

 

[daithi_ie]

That will work - thanks again.

Much appreciated

 

[Fluff]

My pleasure.