**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 Customer Invoices that have stayed in the same Row rank (Column J) for 2 consistent months. The rankings are segmented based on the criteria in Column P.
The sheet I'm working on is labeled Top X and the formula should be inputted in Q200:AZ212. The Row rank is found in column J, the Account ID is column B, and the Date range is in C (note the date range is Text, but the corresponding Dates needed for segmenting the data are located in Q2:AZ2. Please let me know if you have any questions!

Solved by K. Y. in 46 mins

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

Excelchat Expert
14/08/2018 - 02:20

Hi

Excelchat Expert
14/08/2018 - 02:20

Welcome to Got it Pro

Excelchat Expert
14/08/2018 - 02:21

Please share the data and tell more about the requirement so that i can help better

User
14/08/2018 - 02:21

Hello, here is the workbook

[Uploaded an Excel file]

Excelchat Expert
14/08/2018 - 02:22

Please tell a bit more about the requirement

User
14/08/2018 - 02:23

Starting in Q200, I'm trying to get the percentage of invoices that have stayed in the same range for 2 months in a row.

Excelchat Expert
14/08/2018 - 02:24

which sheet?

User
14/08/2018 - 02:24

Top X

User
14/08/2018 - 02:24

Starting on Q200

Excelchat Expert
14/08/2018 - 02:24

What do you mean by stayed in 2 months?

User
14/08/2018 - 02:26

Row J has a list of Rankings called Row. We have given a numerical rank to the highest dollar invoices starting with 1. We are looking to get the percentage of which customer's consistantly had the top Row ranks for 2 months. The desired Rank ranges are indicated in Column P

User
14/08/2018 - 02:28

To break it down more, we need the percentage of Account IDs (B:B) that have the same Row (J:J) range for two consistant months (Date Range = C)

User
14/08/2018 - 02:29

Row 2 of the Sheet has the same date format for reference and the section above Q200, Q185 is a good reference

Excelchat Expert
14/08/2018 - 02:30

Yes i understand, i think i solved Q185 for you

Excelchat Expert
14/08/2018 - 02:30

I gave one session before to you as well and you gave me 5 star for that

Excelchat Expert
14/08/2018 - 02:30

Let me figure this one out as well

User
14/08/2018 - 02:30

Awesome! So glad I got you again!

Excelchat Expert
14/08/2018 - 02:30

Just give me sometime

User
14/08/2018 - 02:30

Of course, let me know if you have questions

Excelchat Expert
14/08/2018 - 02:32

Sure

Excelchat Expert
14/08/2018 - 02:35

You require them to be same in two consequitive months?

User
14/08/2018 - 02:36

That's correct. So column Q doesn't need a result

Excelchat Expert
14/08/2018 - 02:36

Month starts from R

User
14/08/2018 - 02:37

Correct

Excelchat Expert
14/08/2018 - 02:37

You mean R does not need formula

User
14/08/2018 - 02:38

No, Q doesn't need a formula. R should get the percentage for Jan and Feb

Excelchat Expert
14/08/2018 - 02:38

Q has ranges filled in them, it would not have the formula anyway

User
14/08/2018 - 02:39

true

Excelchat Expert
14/08/2018 - 02:40

I am still trying to figure out what % Customers Same (2 mo) mean

User
14/08/2018 - 02:42

So if an Account ID (B) has invoices two months in a row (C) and the Row (J) rank of both invoices fall in the designated range (P), then count and get the percentage of all of those counts

Excelchat Expert
14/08/2018 - 02:44

I think i have figured out a way, let me share a file with you in sometime

User
14/08/2018 - 02:44

Awesome, thank you!

Excelchat Expert
14/08/2018 - 02:50

Are you there?

User
14/08/2018 - 02:51

Yes

User
14/08/2018 - 02:55

any chance you could share via a Google Doc link?

User
14/08/2018 - 02:57

No problem. Would you be able to separate the page and send by itself?

User
14/08/2018 - 02:57

I'm going to keep the time going until we can get the file shared

Excelchat Expert
14/08/2018 - 02:58

This is the file

[Uploaded an Excel file]

Excelchat Expert
14/08/2018 - 02:58

Did you get the file

Excelchat Expert
14/08/2018 - 02:58

Please have a look

Excelchat Expert
14/08/2018 - 02:59

I have written the steps about what i did on sheet 1

Excelchat Expert
14/08/2018 - 02:59

on right

Excelchat Expert
14/08/2018 - 02:59

Do you see it?

Excelchat Expert
14/08/2018 - 02:59

I have added another column for range in which they belong
and have sorted the data by ID, then month and then by range
then have added another column using if statement and have tageed 1 or 0..1 if same as prev month otherwise 0
then have counted that in formula
Not able to reply there as the file is being uploaded i guess

Excelchat Expert
14/08/2018 - 02:59

Please let me know if this helps

Excelchat Expert
14/08/2018 - 02:59

Looking forward to good rating and reviews from You

Excelchat Expert
14/08/2018 - 02:59

It was a tricky question, i must say

User
14/08/2018 - 03:00

Of course, I'll check all the prompted reviews and write you a nice custom one as well. I really appreciate the help

Excelchat Expert
14/08/2018 - 03:01

Thanks a lot

Excelchat Expert
14/08/2018 - 03:01

I am sure this will help you/guide you to go forward

Excelchat Expert
14/08/2018 - 03:02

Did you see the file?

User
14/08/2018 - 03:03

I did not

Excelchat Expert
14/08/2018 - 03:03

Did you download?

User
14/08/2018 - 03:04

it never popped up

Excelchat Expert
14/08/2018 - 03:05

Oh

Excelchat Expert
14/08/2018 - 03:05

It is showing from my side to be delivered

Excelchat Expert
14/08/2018 - 03:05

See this

[Uploaded an Excel file]

Excelchat Expert
14/08/2018 - 03:05

I removed the data

Excelchat Expert
14/08/2018 - 03:05

made it a very small file

Excelchat Expert
14/08/2018 - 03:05

You can just implement it the same way in your excel

User
14/08/2018 - 03:05

Thanks! Loading now

Excelchat Expert
14/08/2018 - 03:05

Just have a look at what i did and let me know if that helps

User
14/08/2018 - 03:06

This will do, thanks!

**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.*