Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Countifs formula
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 I messed up the syntax. I am getting a #VALUE error. Can someone please help me by reviewing the formula below and tell me where I messed up? Thanks! =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") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countifs formula
"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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for multiple countifs | Excel Worksheet Functions | |||
COUNTIFS formula question | Excel Worksheet Functions | |||
Need Countifs Formula Help | Excel Worksheet Functions | |||
Countifs to Sumproduct for 2003 - was Simple Countifs.. from Fr | Excel Worksheet Functions | |||
COUNTIFS Formula Problem | Excel Worksheet Functions |