CountIf for multiple ranges problem.
Posted by Sherwood Botsford on January 23, 2002 10:18 AM
I'm writing a spreadsheet to score an orienteering
meet. I'm new to excel
Each team records the time that it reaches a
particular location, called a control.
They are awarded points on the basis of the order
they reach a control.
So the problem I have is to rank them in the time
they reached that control, if they reached it at all.
For readability reasons, all of a teams data is
kept together, so the time columns are spread out throughout the sheet.
What I'd like to do is: for each team:
=countif($ref1,$ref2,$ref3,$ref4...,"<ref1") - countblanks($ref1,$ref2,$ref3,$ref4...)
refN are references to each team's time column.
They are NOT adjacent.
That is, count how many times are faster that
this team's time. (The count blanks is ncessary
because blank evaluates as midnight when comparing
Right now I'm faced with a terrible kluge looking
something like this: (X is row number)
=(if($Ref1X < Ref1X),0,1)+(if($Ref2X < Ref1X),0,1) ... - (isblank($Ref1X) + isblank($Ref2X)...
(Note: The worksheet is at home. I've probably mucked up the syntax above.)
This is tolerable for the present 4 teams, but now
I have to change all the formulas because there are
6 teams this weekend. There must be a more elegant
way to do this, but it's not clear to me what it is.