Consulting

Results 1 to 11 of 11

Thread: Ok, what am i doing wrong in this one?

  1. #1
    VBAX Regular
    Joined
    Apr 2008
    Posts
    97
    Location

    Ok, what am i doing wrong in this one?

    Been a while so I am rusty once again.
    Want to sum data in dynamic range "profiledata" col 47.
    If it meets the three criteria in the formula.
    1st matches a number in col 16 with one in B8.
    2nd matches a date greater than the date in $e$e
    3rd matches a date less than the date in $d$4

    =SUMPRODUCT(--(INDEX(profiledata,,16)=B8),--(INDEX(profiledata,,46)>$E$4),--(INDEX(profiledata,,46)<$D$4),--(INDEX(profiledata,,47)))
    Do I need an OR in there somewhere as I just get #VALUE! as a result even if entered as an array formula.

    Please help out a rusty fellow

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    It worked fine for me with some data I setup, it certainly does not need array-entering.

    Can you post the offending workbook, my guess the problem is with the definition of profiledata.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Apr 2008
    Posts
    97
    Location
    Quote Originally Posted by xld
    It worked fine for me with some data I setup, it certainly does not need array-entering.

    Can you post the offending workbook, my guess the problem is with the definition of profiledata.
    Quick question, can the data in the col have blanks?

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Yes, in my test most of the data was blanks.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Apr 2008
    Posts
    97
    Location
    Quote Originally Posted by xld
    Yes, in my test most of the data was blanks.
    Here is a cut of the sheet.
    Attached Files Attached Files

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Don't coerce the sum range slang

    =SUMPRODUCT(--(INDEX(profiledata,,46)>=D17),--(INDEX(profiledata,,46)<=E17),--(INDEX(profiledata,,16)=E19),INDEX(profiledata,,47))
    Last edited by Bob Phillips; 08-25-2012 at 10:43 AM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Newbie
    Joined
    Aug 2012
    Posts
    2
    Location
    Set profiledata to $A$2:$FL$13. The mistake is to include the line containing the column names. In this case the function SUMPRODUCT will include cells containing the values ​​of the first row, the row containing the column names, resulting in an error. Because the value of it is invalid to the function.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Quote Originally Posted by Arismar
    Set profiledata to $A$2:$FL$13. The mistake is to include the line containing the column names. In this case the function SUMPRODUCT will include cells containing the values ​​of the first row, the row containing the column names, resulting in an error. Because the value of it is invalid to the function.
    No, you are wrong there. If he had been using the * operator then he would need to avoid the column headers, but as he is using -- there is no need.

    His problem was that he was also using the -- on the range to be summed, --(INDEX(profiledata,,47), and as there were blanks it there it tried to coerce a space to a numeric value, hence the #VALUE.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #9
    VBAX Newbie
    Joined
    Aug 2012
    Posts
    2
    Location

    Smile

    Quote Originally Posted by xld
    No, you are wrong there. If he had been using the * operator then he would need to avoid the column headers, but as he is using -- there is no need.

    His problem was that he was also using the -- on the range to be summed, --(INDEX(profiledata,,47), and as there were blanks it there it tried to coerce a space to a numeric value, hence the #VALUE.
    Yes. I think you are wrong there. The problem occurs because - (INDEX (profiledata,, 47)> = D17 will go through the entire column 47 from the first line to the last. And the error will occur when the function will try to compare the first cell with the cell D17. Try redefine profiledata as I said earlier. I tested it and worked.

  10. #10
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Whatever, believe what you believe.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #11
    VBAX Regular
    Joined
    Apr 2008
    Posts
    97
    Location
    Quote Originally Posted by xld
    Don't coerce the sum range slang

    =SUMPRODUCT(--(INDEX(profiledata,,46)>=D17),--(INDEX(profiledata,,46)<=E17),--(INDEX(profiledata,,16)=E19),INDEX(profiledata,,47))
    Excellent, works perfect!
    I knew there was something small wrong in there somewhere.
    You are the lord

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •