Formula Calc Value with Criteria

[03856me]

I have this contestant worksheet that is repeated many many times, that calculates the "place" based on "time" (column F), which works perfectly. Then I need to calculate the points awarded for members only. I have no problem calculating the points but NOT excluding non-members and blank time. I thought about adding a column calculating the place with the criteria, but the user is adamant that can not happen. Could you please help me add to my current formula for "calc points" or if there is a better way, I am open to that as well.

I cannot change the "Place" column becasue it is correct and is posted as the results of the event. I have added the last two columns (I & J) for this post to show what the correct points (answer) should be.

The formula in column F (Place) =IF($E5>9999,"",IF($C5="","",COUNTIFS($E$5:$E$999,"<"&$E5)+1))
The formula in column G (calc points) =IF([@Member]="No","",IF([@Place]=1,10,IF([@Place]=2,9,IF([@Place]=3,8,IF([@Place]=4,7,IF([@Place]=5,6,IF([@Place]=6,5,IF([@Place]=7,4,IF([@Place]=8,3,IF([@Place]=9,2,IF([@Place]=10,1,0)))))))))))

 

[Fluff]

How about

+Fluff New.xlsm
	C	D	E	F	G
4	Name	Member	time	place	calc points
5	P1	No	13	1
6	P2	yes	13.25	2	10
7	P3	yes	14.1	3	9
8	P4	yes	15	4	8
9	P5	No	16	5
10	P6	yes	16.25	6	7
11	P7	yes	25	7	6
12	P8	No	26.25	8
13	P9	yes	30	9	5
14	P10	yes	ns
Data

Cell Formulas
Range	Formula
F5:F14	F5	=IF($E5>9999,"",IF($C5="","",COUNTIFS($E$5:$E$999,"<"&$E5)+1))
G5:G14	G5	=IF(OR([@Member]="No",[@time]="ns"),"",CHOOSE(COUNTIF(D$5:D5,"Yes"),10,9,8,7,6,5,4,3,2,1))

 

[03856me]

Thank you fluff, you helped me so much last week, this is an extension of that project. I really appreciate your help. I will try your suggestion.

 

[03856me]

The formula for G5 works the way the example was presented - however, the ns could also be any alpha, so only calculate if there are numbers in the time column. Suggestion?

 

[Fluff]

If you can have more than ten members, use

+Fluff New.xlsm
	C	D	E	F	G
4	Name	Member	time	place	calc points
5	P1	No	13	1
6	P2	yes	13.25	2	10
7	P3	yes	14.1	3	9
8	P4	yes	15	4	8
9	P5	No	16	5
10	P6	yes	16.25	6	7
11	P7	yes	25	7	6
12	P8	No	26.25	8
13	P9	yes	30	9	5
14	P10	yes	31	10	4
15	P11	yes	32	11	3
16	P12	yes	33	12	2
17	P13	yes	34	13	1
18	P14	yes	35	14	0
19	P15	yes	36	15	0
20	P16	yes	37	16	0
21	P17	yes	38	17	0
22	P18	yes	39	18	0
23	P19	yes	40	19	0
24	P20	yes	ns
Data

Cell Formulas
Range	Formula
F5:F24	F5	=IF($E5>9999,"",IF($C5="","",COUNTIFS($E$5:$E$1009,"<"&$E5)+1))
G5:G24	G5	=IF(OR([@Member]="No",[@time]="ns"),"",IFERROR(CHOOSE(COUNTIF(D$5:D5,"Yes"),10,9,8,7,6,5,4,3,2,1),0))

 

[03856me]

Again, you are the best and I thank you for your quick response. Solved.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[03856me]

OK, I spoke to quickly, the formula you furnished for G5:G works as long as the Time is sorted Smallest to Largest, but when I sort it by Run number (used real time from the announcers booth) the points change.. Is there a way to calculate it ignoring the sort?

 

[Fluff]

What version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[03856me]

2013