MirageSF

asked on

# 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

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