Unexpected and inconsistent error with ranking rows based on criteria

[strugglingaccountant]

Hi, I have a table similar to the below, and I am trying to use formulae to rank the rows in order, by Total Actual, Budget Variance, Forecast Variance etc. I am wanting the output to be something that says, North 1, North 2, North 3, so that I can use index match on another tab to pull through the specific rows I want. I do not need to account for duplicates, I already have that step working. I am using Office 365 on a Windows computer.

The problem I have is that for some bizarre reason, in the 200 rows of data, five regions, and three comparative scenarios, I have five entries where the formula is kicking out the wrong answer.

As an example, using the below, if I were looking for the project with the largest vs Budget result in North, I would want to see Project C. What is happening is that both Project A and Project C are coming back as being Number 2 in the vs Budget Step 1 column. I have poured through the data, as have two other people, and we cannot understand why we are getting this solitary glitch, when all of the other instances in the vs Budget column are working perfectly. I have manually checked the data and there are no hidden rows for other North projects, and no higher variances in any region for that matter. All formatting appears consistent. A test was done of swapping where the formulae look in columns G & H, and I&J, and the exact same problem occurred. Tried swapping to look at Last Year, and it is only in the specific instance of the vs Budget.

Does anyone have any ideas please on what might be causing this gremlin? There are no hard-codings, all formulae are operational, formula updating is on automatic. Any guidance you can give is greatly appreciated.

If I have forgotten to provide any information, I apologise, please let me know and I will add it in right away.

Many thanks

A	B	C	D	E	F	G	H	I	J
Project	Region	Actual	vs Budget	vs Forecast	vs Last Year	vs Budget Step 1	vs Budget Step 2	vs Forecast Step 1	vs Forecast Step 2
A	North	35	10	5	15	=IFERROR(COUNTIFS(B$2:B$5,B2,D$2:D$5,">"&D2)+1,0)	=B2&" "&G2	=IFERROR(COUNTIFS(B$2:B$5,B2,E$2:E$5,">"&E2)+1,0)	=B2&" "&I2
B	North	50	5	10	20	=IFERROR(COUNTIFS(B$2:B$5,B3,D$2:D$5,">"&D3)+1,0)	=B3&" "&G3	=IFERROR(COUNTIFS(B$2:B$5,B3,E$2:E$5,">"&E3)+1,0)	=B3&" "&I3
C	North	10	15	20	10	=IFERROR(COUNTIFS(B$2:B$5,B4,D$2:D$5,">"&D4)+1,0)	=B4&" "&G4	=IFERROR(COUNTIFS(B$2:B$5,B4,E$2:E$5,">"&E4)+1,0)	=B4&" "&I4
D	South	40	0	20	20	=IFERROR(COUNTIFS(B$2:B$5,B5,D$2:D$5,">"&D5)+1,0)	=B5&" "&G5	=IFERROR(COUNTIFS(B$2:B$5,B5,E$2:E$5,">"&E5)+1,0)	=B5&" "&I5

 

[shift-del]

In my spreadsheet I have no ties with region North.

Mappe11
	A	B	C	D	E	F	G	H	I	J
1	Project	Region	Actual	vs Budget	vs Forecast	vs Last Year	vs Budget Step 1	vs Budget Step 2	vs Forecast Step 1	vs Forecast Step 2
2	A	North	35	10	5	15	2	North 2	3	North 3
3	B	North	50	5	10	20	3	North 3	2	North 2
4	C	North	10	15	20	10	1	North 1	1	North 1
5	D	South	40	0	20	20	1	South 1	1	South 1
Tabelle1

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(COUNTIFS(B$2:B$5,B2,D$2:D$5,">"&D2)+1,0)
H2:H5	H2	=B2&" "&G2
I2:I5	I2	=IFERROR(COUNTIFS(B$2:B$5,B2,E$2:E$5,">"&E2)+1,0)
J2:J5	J2	=B2&" "&I2

 

[strugglingaccountant]

In my spreadsheet I have no ties with region North.

Mappe11
	A	B	C	D	E	F	G	H	I	J
1	Project	Region	Actual	vs Budget	vs Forecast	vs Last Year	vs Budget Step 1	vs Budget Step 2	vs Forecast Step 1	vs Forecast Step 2
2	A	North	35	10	5	15	2	North 2	3	North 3
3	B	North	50	5	10	20	3	North 3	2	North 2
4	C	North	10	15	20	10	1	North 1	1	North 1
5	D	South	40	0	20	20	1	South 1	1	South 1
Tabelle1

Cell Formulas
Range	Formula
G2:G5	G2	=IFERROR(COUNTIFS(B$2:B$5,B2,D$2:D$5,">"&D2)+1,0)
H2:H5	H2	=B2&" "&G2
I2:I5	I2	=IFERROR(COUNTIFS(B$2:B$5,B2,E$2:E$5,">"&E2)+1,0)
J2:J5	J2	=B2&" "&I2

This is the problem I have, that it works perfectly 99% of the time and then there’s just these specific instances where it doesn’t. Tomorrow I’ll strip out the confidential info and upload a copy so you can see.