none
Wrong data type in SUMPRODUCT RRS feed

  • Question

  • Hi,

    I have a frustrating error in a formula that used to work perfectly fine.

    I reduced a very long SUMPRODUCT formula to this, for debugging purposes:

    =SUMPRODUCT(--(CasesTable[Case Type]=$B12),--(CasesTable[Status]=O$8), --(1=1))

    The cell error I'm getting is: A value used in the formula is of the wrong data type.

    If I remove the last argument: --(1=1) from the formula - no error is raised. This works:

    =SUMPRODUCT(--(CasesTable[Case Type]=$B12),--(CasesTable[Status]=O$8))

    Explanations:

    CaseTable[Case Type] holds strings with multiple (validated) possible values, e.g.: YAZ, ACTOS, ....

    CaseTable[Status] holds strings with four (validated) possible values: Active, Dropped, Settled, Cleared.

    Call B12 holds one of the possible values (e.g. "YAZ").

    Call O8 holds one of the four values (e.g. "Dropped").

    (It doesn't matter which values I put in B12 and O8 - the error persists).

    Excel 2013.

    Any idea what's the issue here?

    Thanks!

    Tuesday, April 7, 2015 2:27 PM

Answers

  • Thanks, ryguy72,

    In the end, I changed the formula to have one array with multiplication instead of list of many arrays separated by commas.

    Instead of:

    =SUMPRODUCT(--(CasesTable[Case Type]=$B12),--(CasesTable[Status]=O$8), --(1=1))

    I wrote:

    =SUMPRODUCT(--(CasesTable[Case Type]=$B12) * --(CasesTable[Status]=O$8) * --(1=1))

    And also, I removed spaces and new-line characters I had in the formula string (for readability).

    <link href="chrome://s3gt/skin/s3gt_tooltip.css" rel="stylesheet" type="text/css" />
    • Marked as answer by morsagmon Tuesday, April 7, 2015 6:23 PM
    Tuesday, April 7, 2015 6:23 PM

All replies

  • The double unary is used to convert an internal array of TRUE/FALSE into 1/0.

    I'm not 100% sure, but I'm guessing the '1' is being converted to '-1'. 


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, April 7, 2015 3:31 PM
  • Thanks, ryguy72,

    In the end, I changed the formula to have one array with multiplication instead of list of many arrays separated by commas.

    Instead of:

    =SUMPRODUCT(--(CasesTable[Case Type]=$B12),--(CasesTable[Status]=O$8), --(1=1))

    I wrote:

    =SUMPRODUCT(--(CasesTable[Case Type]=$B12) * --(CasesTable[Status]=O$8) * --(1=1))

    And also, I removed spaces and new-line characters I had in the formula string (for readability).

    <link href="chrome://s3gt/skin/s3gt_tooltip.css" rel="stylesheet" type="text/css" />
    • Marked as answer by morsagmon Tuesday, April 7, 2015 6:23 PM
    Tuesday, April 7, 2015 6:23 PM