Countifs formula
Errata.... I wrote:
=COUNTIFS('Risks source data'!G1:INDEX('Risks source data'!G:G,X1),
'Flagged Leadership'!E15,
'Risks source data'!G1:INDEX('Risks source data'!B:B,X1),
'Flagged Leadership'!R24,
'Risks source data'!G1:INDEX('Risks source data'!C:C,X1),
"<*Closed*",
Table_owssvrCASFE3I1[Exposure],"=2.5")
Copy-and-paste typo. The formula should be:
=COUNTIFS('Risks source data'!G1:INDEX('Risks source data'!G:G,X1),
'Flagged Leadership'!E15,
'Risks source data'!B1:INDEX('Risks source data'!B:B,X1),
'Flagged Leadership'!R24,
'Risks source data'!C1:INDEX('Risks source data'!C:C,X1),
"<*Closed*",
Table_owssvrCASFE3I1[Exposure],"=2.5")
PS: I prefer the form 'Risks source data'!G1:INDEX('Risks source
data'!G:G,X1) over OFFSET('Risks source data'!G1,0,0,X1).
Although the OFFSET expression is much simpler to write and read, OFFSET is
"volatile", whereas INDEX is not. A "volatile" function is recalculated
every time any cell in any worksheet in the workbook is modified.
That might not be too bad if you use OFFSET sparingly. But as a rule, I
avoid "volatile" expressions because they have a tendency to multiply (in
the biblical sense :-) over time.
The choice is yours to make.
PPS: The column range G:G is okay to use as the first parameter of INDEX
because Excel does not really search or process the entire range.
|