+ Reply to Thread
Results 1 to 8 of 8

#VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the data

  1. #1
    Registered User
    Join Date
    08-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the data

    I have the following formula that generates a #VALUE! error whenever blank lines are included in the range being used. Otherwise it works perfectly.

    =SUMPRODUCT(--(DATEVALUE(N7:N100)>$R$12),--(O7:O100))

    Column N contains dates whose format needs to be converted with DATEVALUE. Column O contains values.

    I have tried the possible solutions below. None have worked and the same error keeps being returned.

    =SUMPRODUCT(--(DATEVALUE(N7:N100)>$R$12),--(N7:N100<>""),--(O7:O100))
    =SUMPRODUCT(--(DATEVALUE(N7:N100)>$R$12),--(LEN(N7:N100)>0),--(O7:O100))

    I do need to consider the possibility of blank lines in the data. So, any help on that?

    Thanks in advance,

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the

    Can you show us some examples of what's in column N?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    08-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the

    Dates that come from a system application in text format, but with only 2 digits for the year, like for example: "15/10/13"

    I tried to work with these dates without converting them with DATEVALUE, but the results were not correct. The formula compares the date column against a given date, and should only displays the records whose date is before or equal to this given date.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the

    Try this array formula**:

    =SUM(IF(N7:N100<>"",IF(DATEVALUE(N7:N100)>R12,O7:O100)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Sometimes you can use Data>Text to Columns to convert text dates into true numeric dates.

    Select the range of cells in question
    Goto the Data tab>Text to Columns>Click Finish

    Or

    Goto the Data tab>Text to Columns
    Click Next twice
    On page 3 of the userform, under Column Data format, select Date
    From the drop down list select DMY
    Click Finish

  5. #5
    Registered User
    Join Date
    08-25-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the

    These are elegant solutions, thanks for that.

    But is there a way of doing this with formulas without using arrays?

    Thanks in advance,

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the

    You need the array process to evaluate the IF(range<>"" statement because the results are conditionally evaluated.

    The only thing I can think of is using a helper column:

    Data Range

    M
    N
    O
    P
    Q
    R
    S
    6
    Helper
    Date
    Value
    7
    1
    1/15/2013
    3
    8
    0
    47
    9
    0
    52
    10
    0
    43
    11
    1
    2/25/2013
    81
    Test Date
    Sum Total
    12
    0
    32
    1/1/2013
    84
    13
    0
    12/20/2011
    85
    14
    0
    99
    15
    0
    43


    This formula entered in M7 and copied down:

    =IF(N7<>"",--(DATEVALUE(N7)>R$12),0)

    This formula entered in S12:

    =SUMPRODUCT(M7:M15,O7:O15)

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the

    =SUMPRODUCT(--(DATEVALUE(N7:N100)>$R$12),--(O7:O100))
    Have you tried it without the DATEVALUE function?

    Something like this...

    =SUMPRODUCT(--(--N7:N100>$R$12),O7:O100)

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: #VALUE! error in SUMPRODUCT with DATEVALUE formula, when there are blank lines in the

    or just value
    =SUMPRODUCT(--(VALUE(N7:N100)>$R$12),--(O7:O100))
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Run time error if DateValue textboxes are blank
    By michelle 1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2013, 08:55 AM
  2. [SOLVED] DateValue returns error
    By Debatewise in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-21-2013, 10:11 AM
  3. [SOLVED] Datevalue - Same value, one returns error?
    By Jaymond Flurrie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-08-2013, 11:35 AM
  4. Datevalue #Value error?
    By a94andwi in forum Excel General
    Replies: 4
    Last Post: 03-20-2007, 08:37 AM
  5. Sumproduct Datevalue Problem
    By andyp161 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-09-2006, 11:15 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1