asked on
Adapt following formula to exclude certain info from count
=RANK(D1,DATES,1)+COUNTIFS
B1 - B100 = NAMES OF PEOPLE (RANGE IS NAMED AS NAMES)
D1 - D100 = DAYS THEY STARTED (RANGE IS NAMED AS DATES)
Formula above ranks them in order 1- 100, and if they started on the same day then it will rank the one whose surname is first.
It works great...
However I now need to modify it so it looks at Column N (Named as MODE) if this mode contains A or B then we RANK them, if its anything else then its not included.
But I tried lots of ways and cannot get it to work, any ideas? Thanx
B1 - B100 = NAMES OF PEOPLE (RANGE IS NAMED AS NAMES)
D1 - D100 = DAYS THEY STARTED (RANGE IS NAMED AS DATES)
Formula above ranks them in order 1- 100, and if they started on the same day then it will rank the one whose surname is first.
It works great...
However I now need to modify it so it looks at Column N (Named as MODE) if this mode contains A or B then we RANK them, if its anything else then its not included.
But I tried lots of ways and cannot get it to work, any ideas? Thanx
Microsoft ExcelMicrosoft Office
Last Comment
Ejgil Hedegaard
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Works great not sure how without using rank function but does the job thx
The first 2 countifs, count the number of rows with a date before the actual date, where Mode is A or B.
Countifs use AND for all conditions, so to make A or B, two countifs are used.
Then 1 is added for the actual date.
That is similar to what the rank function does, but rank does not work with multiple ranges.
The last 2 countifs are the offset for equal dates, just like your formula.
Countifs use AND for all conditions, so to make A or B, two countifs are used.
Then 1 is added for the actual date.
That is similar to what the rank function does, but rank does not work with multiple ranges.
The last 2 countifs are the offset for equal dates, just like your formula.