rank.eq with multiple if conditions

[adamelston]

Hi, can anyone provide guidance/examples on rank.eq formula with multiple conditions?

I am generating pivot table for Company A filtered by year, and I want to set up a linked table - table 2 (so row values change when my pivot filters change) to display rank.eq for each row value generated in the pivot, relative to the cohort (e.g. all companies in 'Year', 'Region'), from which my filtered company is taken.

I am guessing this would be formula in table 2 comprising a combination countifs/rank.eq formula looking up 'Year', 'Region', 'All', 'F', 'M' and returning in each case the rank.eq of company A?

table 1. Pivot table
Year = 2019

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Company A (England)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]All %[/TD]
[TD]F %[/TD]
[TD]M %[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

table 2. Linked table showing rank.eq for Company A row values relative to all companies in a region

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Region "England", Year "2019"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]All %[/TD]
[TD]F %[/TD]
[TD]M %[/TD]
[/TR]
[TR]
[TD]Red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Blue[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Green[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hope this make sense and any help on how to write this would be appreciated, many thanks!

A

 

[James006]

Hello,

Have you tried an array formula with all your conditions ... ?

 

[adamelston]

Hi, no, but I will do - I'm struggling at the moment with the formula to combine the rank.eq with the conditions.