Countifs formula

[crazymazy]

I wrote the formula below to count the number of items in the 'Exposure
column that have a value greater than or equal to 2.5.

The formula must be based on 3 conditions:

1.) Column G equals a value
2.) Column B equals a value
3.) Column C does NOT equal "Closed" ---- I'm thinking this is where
messed up
the syntax.


I am getting a #VALUE error. Can someone please help me by reviewing th
formula below and tell me where I messed up?

Thanks!



=COUNTIFS('Risks source data'!G:G,'Flagged Leadership'!E15, 'Risk
source data'!B:B,'Flagged Leadership'!R24,'Risks sourc
data'!C:C,"<>*Closed*", Table_owssvrCASFE3I1[Exposure], ">=2.5"

 

[joeu2004]

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.

 

[joeu2004]

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