# Excel - COLUMN Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.