"crazymazy" wrote:
I am getting a #VALUE error. Can someone please help me by
reviewing the formula below and tell me where I messed up?
[....]
=COUNTIFS('Risks source data'!G:G,'Flagged Leadership'!E15,
'Risks source data'!B:B,'Flagged Leadership'!R24,
'Risks source data'!C:C,"<*Closed*",
Table_owssvrCASFE3I1[Exposure], "=2.5")

Probably because the range specified by Table_owssvrCASFE3I1[Exposure] is
not an entire column as G:G, B:B and C:C are.

Actually, column ranges like G:G are a bad idea anyway, IMHO. In this case,
they cause Excel to evaluate 3+ million comparisons: G:G=E15, B:B=R24, and
C:C<"*closed*".

It is very unusual that we might actually have 1+ million rows of data.

However, it is difficult to say how to fix this based on the size of
Table_owssvrCASFE3I1[Exposure]. Ostensibly, we might use ranges like:

'Risks source data'!G1:INDEX('Risks source
data'!G:G,COUNTA(Table_owssvrCASFE3I1[Exposure]))

But that presumes there are no empty cells in the range specified by
Table_owssvrCASFE3I1[Exposure].

Also, that is inefficient because you would need to use
COUNTA(Table_owssvrCASFE3I1[Exposure]) three times.

It might be better to have =COUNTA(Table_owssvrCASFE3I1[Exposure]) in a
cell, say X1. Then you could write:

=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")

Instead of using X1, you might consider a defined name whose "Refers to"
field is =COUNTA(Table_owssvrCASFE3I1[Exposure]).

However, that defined name is "volatile": the defined name formula is
recalculated every time any cell in any worksheet in the workbook is
modified.

In contrast, the formula in X1 is recalculated only when any cell in the
range specified by Table_owssvrCASFE3I1[Exposure] is modified.