• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 55
  • Last Modified:

Adapt following formula to exclude certain info from count

=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
0
MirageSF
Asked:
MirageSF
  • 2
1 Solution
 
Ejgil HedegaardCommented:
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

0
 
MirageSFAuthor Commented:
Works great not sure how without using rank function but does the job thx
0
 
Ejgil HedegaardCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now