MirageSF
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
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
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.
ASKER