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]

Thanks for that, try

+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	ns
14	P10	yes	31	9	5
15	P11	yes	32	10	4
16	P12	yes	33	11	3
17	P13	yes	34	12	2
18	P14	yes	35	13	1
19	P15	yes	36	14	0
20	P16	yes	37	15	0
21	P17	yes	38	16	0
22	P18	yes	39	17	0
23	P19	yes	40	18	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(COUNTIFS([Member],"Yes",[place],"<"&[@place])+1,10,9,8,7,6,5,4,3,2,1),0))

Don't forget to update your account details to show your version of Excel.

 

[03856me]

Thanks Fluff, appreciate the help

 

[Fluff]

You're welcome & thanks for the feedback.