DoIf function

Range Operations category - Range Sum family Home 
What the name means"Do an operation, such as SUMIF, across several sheets"
SyntaxDoIf ( Operation, ListIn, [ListOut], Criteria, [CritOpts], [DefValOnErr] )
Description
VolatilityThis function is termed 'volatile', which means it recalculates whenever the worksheet is recalculated, regardless of whether any of its inputs have changed. See Volatility.
See AlsoFindIf (InpList, OutList, Criteria, [FindSwitch], [CritOpts])
VariableType or ValueDescription
Inputs: (6)
1 Operation Integer (Optional) Switch governing which operation you want done (Optional)
2 ListIn Any A 1 dimensional range. It can span several sheets eg Sheet1:Sheet2!A1. This is the input range against which the tests are run.
3 ListOut Any (Optional) A 1 dimensional range. It can span several sheets eg Sheet1:Sheet2!A1. This is the range that goes into the calculations or is the output range (analogous to Excel's 'sum_range'). If missing, the input range is used instead. (Optional)
4 Criteria Range of Poly (Optional) One or more criteria written in the BF style of writing criteria.
5 CritOpts Integer (Optional) One or more Search Options (additive) (Optional)
6 DefValOnErr Any (Optional) Default Value on error (Optional)
Output:
Return Value ReturnVar Any The return value
Defaults and Values

The variable(s) of this function have certain defaults and/or switch values associated with them. Click here to see more.

Range Handling and Constraints

This function has range inputs that are subject to range handling procedures to help reduce input errors and increase flexibility and speed. Click here to see more.

BF Style Criteria

BF style criteria are similar to Excels but their behavour has been extended with the use of the '?' operator to signify the value being tested. The ways to input criteria are:
  • Simple Criteria using >,<,>=,<=, <> and =. These can either be a text argument (">10") or a written in a cell that is referred to in the function ie >10. The criteria are entered in a cell as text (even if you just type in >2, Excel actually understands this internally as text), or entered directly as a text string in a function such as DoIf. You can preface with a ' or leave Excel to interpret them as text.
  • Simple Values that imply an 'equals to', eg 10 written in a cell. These can be entered either as text, date or number or directly into the argument list of a function such as DoIf
  • Developed Criteria, which use the value tested against in more complex ways, perhaps using Excels own functions, such as MOD(?,10)=0. In this way you can use AND() and OR(), eg AND(MOD(?,10)=0,MOD(?,3)=0). The criteria are entered in a cell as text or directly into the functions argument list, and you can preface with a ' or leave Excel to interpret them as text. The value tested against is the '?' and the function internally subsitutes the value into any place (and there can be more than one) where there is a '?'. Developed criteria do not accept cell references or range names however. Instead, you have to convert the contents of what are comparing against to text, for example, with the range names CurYear and EndYear: =DoIf(0,$P$8:$P$38,S8:S38,"and(?>"&TEXT(CurYear,"#")&",?<="&TEXT(EndYear,"#")&")")
  • A List of Criteria. A range of cells is permitted as Criteria for further OR conditions (the default). So each cell represents an 'OR' condition. If you want each cell to be an AND condition, see the variable CritOpts.
Whats good or unique about it
DoIf and its sister function FindIf are currently the only function in the BF library that can deal with Multi-Sheet references, such as 'Sheet1:Sheet3!D5'. This means that the Operation it does, and the most common is SUMIF, can be done across sveral sheets, giving it unique functionality. Excel really lacks the ability to do anything across sheets, and most functions cannot cope with multi-sheet references, with the notable exception of SUM(). Unfortunately, Excel's SUMIF() won't work across sheets, which is where úDoIf is particularly useful. If we tell you why Excel doesn't do multi-sheet references you will understand the issue more. Excel's basic cell storage mechanism doesn't understand multi sheet references as described previously - internally it assumes that a cell reference has a single sheet that it refers to, so multi-sheet references just aren't in its vocabulary. We don't know how SUM() does it, actually. DoIf has to re-parse the entire formula string of the cell, to pick out its own arguments, and with the help of Excel's parser, interpret them. That is why currently you can only have one DoIf in a cell - we just weren't clever enough to write a multi-function parser. Its also why you should watch the performance of this function a bit, particularly over long sheet ranges - although so far it has appeared to us to be quite fast.
For Excel Experts
This function has a coupleof rather unique properties.

Firstly it can work on cross-sheet arrays ie 3D ranges (within certain limitations). In this respect it is a like a handful of functions such as SUM(), within Excel.

It can also refer to itself though, something that would normally create a circular reference. This means that you can use it to pick off values in a 3D range when It itself is part of the range. We used it to calculate intercompany flows from different worksheets.

It is interesting to note why it can do this. A circular reference or error would/should occur but it doesn't because the function never actually tries to evaluate the range you send it. Instead, it looks at the whole functioncall, in string form, extracts the range details, and then evaluates them. It never actually evaluates the reference you passed directly, and in this respect it is more like EXCEL's INDIRECT().

This unique behaviout also alerts you to a potential performance problem with this function when used (as it often is) frequently. That parsing of the input reference does take time, and whilst we don't know whether this matters or not, be on the look out for the model getting a bit slower if you use this function a lot.
Tip
DoIf works across worksheets, in other words in accepts multi-sheet references. Not many functions either in Excel or BF do this. Because of its ability to do a SUMIF across sheets, we've found occasions where it would be useful to have the function default, say to zero, if it encounters an error. That's why there is an optional DefValOnErr variable in this function, that you can have triggered when an error is encountered. If you specify a default, it is applied on an item by item basis to the Lists: if an error is encountered amongst the list items entered, then the value of that item within the Lists array is set to the default.
Examples
  • Preview of the DoIf example.
  • The above spreadsheet can be accessed from the Quick Reference (on the Business Functions menu), on the Examples menu, or in the Business Functions\Component Examples directory