1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF within SUMPRODUCT

Discussion in 'Ask an Excel Question' started by Naceur, Sep 9, 2017.

  1. Naceur

    Naceur New Member

    Messages:
    7
    Hi all,

    I'm trying to use the IF function within the SUMPRODUCT function. I'm getting some weird results; (#VALUE!) error.

    Can any one have a look at the sample file and let me know your thoughts?
    Thanks

    Attached Files:

  2. Ramesh

    Ramesh Active Member

    Messages:
    159
    You have to press Ctrl + Shift + Enter after typing in the formula.. when I did that on your file.. I can see the error is gone
    Thomas Kuriakose likes this.
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    SUMPRODUCT as an array formula does not need to be entered using CTRL SHIFT ENTER only if it does not contain any other function which requires to be entered using CTRL SHIFT ENTER to work correctly on arrays.

    In your case , the IF function requires the usage of CTRL SHIFT ENTER to operate correctly on arrays.

    By embedding this function within the SUMPRODUCT function , the formula itself requires to be entered using CTRL SHIFT ENTER , failing which it will not work correctly on arrays of data , and will not perform as an array formula.

    Narayan
    Khalid NGO and Thomas Kuriakose like this.
  4. Naceur

    Naceur New Member

    Messages:
    7
    Hi,

    @ Ramesh: Thank you very much for your solution. I actually wanted to avoid array formulas. I have a database of accounting entries of 250k lines. Array formulas may make it a very slow file to calculate.

    @Narayan: If the IF function does not output an array, then why the formula is working when cell C14 has the value "ALL" (see result in cell E14)?

    Also, if you select cell E12 and click on the "fx" sign on the formula bar, you can see that the result of the IF function is {1;1;0} ... doesn't that mean that the IF function IS generating an array?
    Also if you select the whole IF function (in edit mode) and press F9, you can see that the result of the if function is an array.

    I still think the behavior is weird and not consistent ... what do you think?

    Regards,
    Naceur
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi ,

    Your IF statement is :

    IF($C12="Any", {1;1;1}, ($C$4:$C$6=$C12)*1)

    When C12 is put to Any , the result of the IF statement is a literal array , which means you have specified the array.

    When C12 is put to any other value , the result of the IF statement is the array formed by the expression ($C$4:$C$6=$C12) , and this will result in an array only when the formula is entered using CTRL SHIFT ENTER.

    To verify this , in any unused cells , enter the following two formulae , using only the ENTER key :

    =SUM(IF($C12="Any", {1;1;1}, ($C$4:$C$6=$C12)*1))

    =SUM(IF($C14="Any", {1;1;1}, ($C$4:$C$6=$C14)*1))

    The result of the first formula should be 2 , while the result of the second formula should be 3.

    Check out the results for yourself.

    Re-enter the first formula as an array formula , using CTRL SHIFT ENTER , and see the result.

    Narayan
    Thomas Kuriakose likes this.
  6. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,317
    Maybe , use "+" instead of IF within the SUMPRODUCT function

    In F12,

    =SUMPRODUCT(--($B$4:$B$6=$B12),($C12="any")+($C$4:$C$6=$C12),--($D$4:$D$6=$D12),$E$4:$E$6)

    Edit :

    1] In $C12 ="OAAA"

    =($C12="any")+($C$4:$C$6=$C12)

    =0+{1;1;0}

    ={1;1;0}

    2] In $C14 ="any"

    =($C14="any")+($C$4:$C$6=$C14)

    =1+{0;0;0}

    ={1;1;1}

    Regards
    Bosco

    Attached Files:

    Last edited: Sep 11, 2017
  7. Naceur

    Naceur New Member

    Messages:
    7
    Hi,

    I still don't understand why the IF function generates an array when I click on the fx symbol on the formula bar. It even gives the correct final result (-116) [see image below].... maybe Excel is assuming it's an array formula before I even hit the Ctrl+Enter

    [​IMG]

    ... but if that's the way it works then that's the way it works ... and I should bear that in mind for future formulas.

    Thanks again Narayan

    Regards,
    Naceur
  8. Naceur

    Naceur New Member

    Messages:
    7
    You know what, I just tried with other simple formulas.
    Ex: Sum ( (.....) * (....) * (....)) [with and without Ctrl+Enter]

    Excel always gives the correct result if you open the dialog box by hitting the fx on the formula bar ... but if you don't use Ctrl+Enter then it shows an error result.

    I guess I'm getting confused, that's all.

    Thanks for your input

    Ragards,
    Naceur
  9. Naceur

    Naceur New Member

    Messages:
    7
    Hi Bosco,

    Thannnnks very much ... that's working perfectly
    That way, I can avoid the array formulas

    Naceur
  10. David Evans

    David Evans Active Member

    Messages:
    647
    As @NARAYANK991 pointed out, you are using an Array formula when you use SUMPRODUCT - it's just that you don't have to enter it using the CTRL SHIFT ENTER. So if you think you're avoiding Array formulas ....
  11. Naceur

    Naceur New Member

    Messages:
    7
    @David: I always was "under the impression" that Sumproduct is "lighter" than array formulas requiring CSE.
    Thanks for pointing out that, though.
  12. Lori

    Lori Active Member

    Messages:
    107
    @Naceur: Your example does raise some interesting points. Here is my 2 cents:

    Ctrl+Shift+Enter. As others have said fixes the issues, but may be a bit slower to recalculate. In my experience even adding a few extra functions to avoid CSE can be faster and is also more efficient when copying down rows.

    Function wizard: The fx tool evaluates functions in isolation not in the context of the formula as entered on the sheet. The result shown in the formula is also what you get from entering in VBA immediate window:
    Code (vb):
    ?evaluate(activecell.Formula)
    Evaluate Formula: This tool is the only reliable way to debug a formula. Stepping through shows that without CSE $C$4:$C$6 is evaluated by "implicit intersection", i.e. by returning the intersection of the range from the same row or column. This returns #VALUE! as there is no intersection with row 12 so the overall formula returns an error. This part of the formula could be replaced by INDEX(($C$4:$C$6=$C12)*1,) to avoid CSE

    IF function. This is a tricky function to understand in some ways as it is one of only a few functions that allows for any type of data (value, array or reference). The function wizard is showing that IF can return an array with an array input. The default return value of IF is a value, however, which means that CSE is usually needed to convert it to an array.

    As a simple example consider the two formulas below:

    =SUM(IF(1,{1;1;1}))
    =SUM(ABS({1;1;1}))

    While both return 3, clicking the fx tool indicates that the IF function is applied once to the whole array ({1;1;1}), whereas ABS and other similar functions that take value only arguments are applied multiple times - by iterating over each value (1).
  13. Lori

    Lori Active Member

    Messages:
    107
    i thought of a better example to illustrate the "trickiness" of the IF function...

    Suppose you want to find attendance for a course from first day to last in columns A and B (where days attended are marked with an "x" and blank otherwise):

    01-Sep
    04-Sep x
    05-Sep x
    06-Sep
    07-Sep x
    08-Sep
    11-Sep x
    12-Sep
    13-Sep

    One option is to enter:

    =SUM(INDEX(A:A,MATCH("~x",B:B,IF(1,{0,-1})))*{-1,1},1)

    This returns 8 days (04-Sep to 11-Sep inclusive). However replacing IF(1,) with SIGN() or N() in the formula results in 1.

    So what is the purpose of the IF(1,)?

    (Basically this is due to IF(1,) returning an array/reference combination, and not just an array.)
  14. Naceur

    Naceur New Member

    Messages:
    7
    @Lori : wow, thanks very much. This is a lot of new tricks for me.

    I liked your explanation about the "implicit intersection". I think this could be helpful to understand other functions.

    With regard to your formula to find attendance, I'd like to ask the following:
    • what's the role of the Tilde sign before the "x"? .. "x" is not a wild card, is it? .. I removed the tilde and the result becomes 1
    • I don't understand how the MATCH Function returns an array? and how it deals with the IF result. As a matter of fact, if I change the IF block with the result {0,-1} - (Actually, it is always equal to {0,-1}) - then I get 1 as the final result of the whole formula ... why?
    • Can the INDEX Function return an array?
    I'm trying to learn from you here ... Thanks
  15. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,817
    Hi Lori ,

    I am confused by the following :

    Your statement :
    I assume then that days which do not have an "x" marked against them are not to be counted as attendance ; is this correct ?

    If yes , then should the result for the data you have given be 4 or 8 ?

    If it is 8 , then essentially the "x"s marked against the dates Sept 5 and Sept 7 do not matter , since the result would be 8 even if these two "x"s were removed ; is this correct ?

    Narayan
  16. Lori

    Lori Active Member

    Messages:
    107
    @NARAYANK991, Yeah that wasn't phrased very clearly. What i meant was the time span from first day to last day. Another interpretation could be length of a work contract from first day to last worked and blanks are vacation or sick days.

    @Naceur, you could also use "*" in place of "~x" (but then it might change the result if a header row were present). The formula result has to do with the combinations of types of data involved - i will try and provide a more detailed explanation when time permits.
    Naceur likes this.
  17. Lori

    Lori Active Member

    Messages:
    107
    Here are a few further notes relating to the questions raised above...

    First off, contrary to what I said previously, tests on various formulas in the attachment indicate there is no significant difference with using CSE on formulas and that multi-cell array formulas can be fastest for returning a set of values from a list (tested with Office 2016 64 Bit.)

    The INDEX/MATCH functions are each executed twice inside the formula, once with match_type = 0 and once with match_type = -1. These two cases treat the tilde differently.

    match_type=0. The tilde is just an escape character for wildcards (*,? and ~) it has no effect on other characters. So in this case "~x" = "x" and the first match is returned.

    match_type=-1. The tilde is matched according to the descending sort order. Since "~x" < "x", the last match is returned.

    One way to find out the types of input / output of the MATCH and IF functions is:

    1. Open the function wizard to show the basic input types: value (number, logical, text, any), reference or array...

    =MATCH(any,number,number)
    =IF(logical,any,any)

    2. Enter a {1} for each input in the function wizard and check what is displayed next to the input box to see if array is an input/output type...

    =MATCH({1},{1},{1}) -> MATCH(1,{1},1) =1
    =IF({1},{1},{1}) -> IF({1},{1},{1}) ={1}

    3. Enter A1 in each input and then step through with the evaluate tool to see if references get converted to values, if not then input/output is a reference type...

    =SUM(MATCH(A1,A1,A1)) -> SUM(MATCH(1,A1,A1)) -> SUM(1)
    =SUM(IF(A1,A1,A1)) -> SUM(IF(1,A1,A1)) -> SUM($A$1)

    I find applying formatting for value, reference, array can help visualise things:

    MATCH ( lookup_value, lookup_array, match_type )

    IF ( logical_test, value_if_true, value_if_false )

    Passing the IF result to the match_type argument forces an array return. It turns out this combination of Array+Reference output together with the Value+Reference input can be applied to return an array in general. (A complete list of functions with types was recently posted in the vault forum for reference.)

    In effect, the array is being passed by reference in the match_type argument when IF(1,{0,-1}) is inserted as opposed to by value when just {0,-1} is used.

    Yes, INDEX can return an array when the second or third argument is zero. The reference form of INDEX (in the notation above) is:

    INDEX ( reference, row_num, column_num, area_num )

    Because INDEX can return arrays it does not process arrays within value arguments. (According to OpenFormula documentation "Functions returning arrays are not eligible for implicit iteration").

    So it seems any of the value inputs (row_num, column_num or area_num) need to be coerced to an array using a method similar to the MATCH+IF combination.

    Attached Files:

    Last edited: Sep 19, 2017
    Thomas Kuriakose and r2c2 like this.
  18. r2c2

    r2c2 Active Member

    Messages:
    125
    This is amazing Lori. Thanks for posting. I have always struggled to return parts of an array thru INDEX and the MATCH + IF combination looks interesting.

Share This Page