**Question description:**

*This user has given permission to use the problem statement for this blog.*

I'm trying to write a formula that would find the percentage of Invoices that have stayed in the same Row rank for 2 months. The sheet I'm working on is labeled Top X and the formula should be written starting in Q200. The Row rank is found in column J, the Account ID is column B, and the Date range is in C.

Solved by G. J. in 60 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
14/08/2018 - 02:18

Hi welcome!

User
14/08/2018 - 02:18

Hello! Here's the workbook

[Uploaded an Excel file]

Excelchat Expert
14/08/2018 - 02:20

Ok, I have the sheet open

Excelchat Expert
14/08/2018 - 02:21

Let me review the request

User
14/08/2018 - 02:21

No problem, let me know if you have any questions

Excelchat Expert
14/08/2018 - 02:23

column J -row rank, where is the month indicator?

User
14/08/2018 - 02:24

Q2:AZ2

User
14/08/2018 - 02:24

And Row C has Year and Month

Excelchat Expert
14/08/2018 - 02:25

But how do they relate to col J?

Excelchat Expert
14/08/2018 - 02:26

J200 = 273; is that ranked 1-10?

User
14/08/2018 - 02:27

The data was pulled from 18 months worth of invoices. Each Row is a rank based on the Invoice amount. We're trying to calculate what percentage of the invoices were constantly in the same Row

User
14/08/2018 - 02:28

So if a Row has 1, then it is the highest dollar amount contract that month

User
14/08/2018 - 02:29

Correspondingly, if the Row column = 100, it's the 100th highest dollar invoice that month

Excelchat Expert
14/08/2018 - 02:30

so cell J200 = 273, that means 273rd ranked amount, but for what month?

User
14/08/2018 - 02:30

That's correct and it's 201805 = March 2018

User
14/08/2018 - 02:30

Sorry, May

User
14/08/2018 - 02:31

yyyymm

Excelchat Expert
14/08/2018 - 02:32

column c has the yyyymm

Excelchat Expert
14/08/2018 - 02:32

ok

User
14/08/2018 - 02:32

Yup, C is the date

Excelchat Expert
14/08/2018 - 02:33

Ok, got the problem statement

Excelchat Expert
14/08/2018 - 02:33

Now need to find a formula or two

Excelchat Expert
14/08/2018 - 02:35

and need to search the entire range- 238K rows?

User
14/08/2018 - 02:36

Yup, Q 185, the yellow section right above, is the correct format

User
14/08/2018 - 02:37

It also has most of the logic... I just couldn't figure out how to search more than 1 month

Excelchat Expert
14/08/2018 - 02:37

Q185 is blank

User
14/08/2018 - 02:38

=COUNTIFS($J$4:$J$238732,"<=10",$C$4:$C$238732,Q$2)/COUNT($C$5:$C$238732)

User
14/08/2018 - 02:39

I must of sent you an old file. that's the solution above, though

Excelchat Expert
14/08/2018 - 02:39

ok, makes the denominator clear

User
14/08/2018 - 02:39

No worries, it's a really weird problem

Excelchat Expert
14/08/2018 - 02:41

So if J200=273 , was also 273 in DEC 15, it is counted?

User
14/08/2018 - 02:43

it's still in the 201-500 range

Excelchat Expert
14/08/2018 - 02:44

Ok, so really looking at P for the range bracket

User
14/08/2018 - 02:44

Yes

Excelchat Expert
14/08/2018 - 02:45

And dates Q2 example are not standard dates but text

User
14/08/2018 - 02:45

Yah, you can use Row 2 to match

User
14/08/2018 - 02:46

It has the corresponding text date

Excelchat Expert
14/08/2018 - 02:50

are we allowed to add helper columns?

Excelchat Expert
14/08/2018 - 02:50

I think this is too much for a single formula

User
14/08/2018 - 02:50

Yah, I'm the manager lol

Excelchat Expert
14/08/2018 - 02:50

at least to get started

Excelchat Expert
14/08/2018 - 02:54

We may run out of time, so this is how I think we can do this

Excelchat Expert
14/08/2018 - 02:55

create a helper column with ID&Rank Bracket& month

User
14/08/2018 - 02:56

I actually have the unlimited plan if you don't mind giving it a shot for the next 20 min. I've been working on this all day and . can't seem to make anything work lol

Excelchat Expert
14/08/2018 - 02:56

search where current rank = previous month rank

Excelchat Expert
14/08/2018 - 02:56

Ok, I will keep at it

Excelchat Expert
14/08/2018 - 02:57

If we run out of time , I will continue and send via site support

Excelchat Expert
14/08/2018 - 03:02

Sorry, lost the flow here

Excelchat Expert
14/08/2018 - 03:02

How do I know J200 =273 was in the same bracket last month?

User
14/08/2018 - 03:03

i would think a variation of a countif statement

Excelchat Expert
14/08/2018 - 03:04

These brackets are not consistent

User
14/08/2018 - 03:04

if Row is between blank and blank this month and the next month, count

Excelchat Expert
14/08/2018 - 03:04

what is blank and blank- different for 273

Excelchat Expert
14/08/2018 - 03:05

vs 1500

Excelchat Expert
14/08/2018 - 03:05

we can add a table that looks up bracket?

User
14/08/2018 - 03:06

Yah, that would be fine

Excelchat Expert
14/08/2018 - 03:12

Ok, table built

Excelchat Expert
14/08/2018 - 03:16

Ok, I think I have enough info to start

Excelchat Expert
14/08/2018 - 03:16

I will need to finish , and send via site support

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*