Adapt following formula to exclude certain info from count

MirageSF
MirageSF used Ask the Experts™
on
=RANK(D1,DATES,1)+COUNTIFS(DATES,D1,NAMES,"<"&B1)


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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this
=IF(OR(N1="A",N1="B"),COUNTIFS(Dates,"<"&D1,Mode,"A")+COUNTIFS(Dates,"<"&D1,Mode,"B")+1+COUNTIFS(Dates,D1,Names,"<"&B1,Mode,"A")+COUNTIFS(Dates,D1,Names,"<"&B1,Mode,"B"),"")

Open in new window

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial