+ Reply to Thread
Results 1 to 20 of 20

Array formula error

  1. #1
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Array formula error

    =SUM(IFERROW(--sheet1A2:D2,0))

    This the formula that i got from one of the users but for some reason this formula works on sone cells wheb i drag it down.. any idea why and how to fix it?

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

    Re: Array formula error

    IferroW?
    Is that a typo?
    "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

  3. #3
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,429

    Re: Array formula error

    You may need to upload a spreadsheet to give it some context. Right off hand IFERROW is spelled wrong but I'm not sure if that is just a typo here, if not it should be IFERROR.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  4. #4
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Re: Array formula error

    Nope not a typo

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,429

    Re: Array formula error

    well you've got both mine and Martin's replies, change it to IFERROR and see if that works.

  6. #6
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Re: Array formula error

    Yes my bad it was a typo, well the issue is this. When i drag down the formula in sheet 2, the formula does not work for some cells..
    Attached Files Attached Files

  7. #7
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,429

    Re: Array formula error

    I'm not sure from your attachment what you are trying to sum. H3:J3 in sheet 1 only contains a date.
    How were you planning to sum dates?

  8. #8
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Re: Array formula error

    as you can see, i have dates in different columns and i wanted all dates under one column in a different sheet. I posted regarding this and that was the formula i got.

    than i this issue popped up, it works on some cell..

  9. #9
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Re: Array formula error

    as you can see, i have dates in different columns and i wanted all dates under one column in a different sheet. I posted regarding this and that was the formula i got.

    than i this issue popped up, it works on some cell..

  10. #10
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Array formula error

    I think I see what you're doing..

    On sheet 1, there will be only 1 date per row in columns H through J
    And you want to find that date.

    The problem is that many of your dates on Sheet1 are not really dates, they're just text strings.
    Example, H4 shows 16/01/1982.
    In US style dates (mm/dd/yyyy), that isn't a real date. There is no 16th month.
    So in the formula on Sheet2, A2
    =SUM(IFERROR(--Sheet1!H4:J4,0))
    It is returning 0, because it was an error..
    0 formatted as a Date is 1/0/1900


    You need to correct the dates on Sheet1.
    The ones that appear LEFT alligned are not really dates.
    The ones that appear RIGHT alligned ARE real dates.


    Hope that helps.

  11. #11
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Re: Array formula error

    Yes your correct, well do you know an easy way to correct the errors because my original sheet contains 239,000 rows..

  12. #12
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,429

    Re: Array formula error

    Jonmo1's observation is what I was working on and I agree that is what is hanging things up.
    So is all of your data in dd/mm/yyyy format? Can you convert the data it comes from into mm/dd/yyyy format?

  13. #13
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Array formula error

    Well it depends on which date format your system has..
    Some dates are real, and some aren't.
    For any corrective action, we need to know which is which..
    And it can be different from one pc using UK dates (dd/mm/yyyy) and another using US dates (mm/dd/yyyy).


    So looking at YOUR pc, give a few examples of both..
    A few that show LEFT alligned, and a few that show RIGHT alligned.

  14. #14
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Array formula error

    This will take all the dates from columns H, I and J of Sheet 1 and put them in column A of sheet 2. There is however a problem with the dates on sheet1 in that some are real dates and others are text dates. Select column I and click on the Data tab, Text to Columns, Delimited, Next, Next, Select DATE, click FINISH. Repeat for columns I and J. This will convert all dates to real dates.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  15. #15
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Re: Array formula error

    well i tried changing the format but it doesn't change..

  16. #16
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Array formula error

    Quote Originally Posted by vonmunchy View Post
    well i tried changing the format but it doesn't change..
    Of coarse not.
    Changing the format of a cell only changes the way the contents of that cell is displayed.
    It doesn't actually cange the value of the contents of the cell.

    If the contents of the cell isn't really a date to begin with, changing the format won't fix it.
    We have to fix the contents of the cell.

    And we need to know which is which. See post #13.

  17. #17
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Array formula error

    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  18. #18
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Array formula error

    This is your workbook with the dates converted and the formula applied
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    04-10-2013
    Location
    Maldives
    MS-Off Ver
    Excel 2016
    Posts
    39

    Re: Array formula error

    Thank you everyone for all your help. Really appriciate your quick responses

  20. #20
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Array formula error

    Thank you for the feedback.

+ 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] #VALUE! Error In With Array Formula
    By DDM64 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-29-2013, 11:17 AM
  2. Array formula error
    By tkellymd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-14-2012, 02:50 PM
  3. #N/A Error in Array Formula
    By jrarmstrong4 in forum Excel General
    Replies: 7
    Last Post: 11-30-2011, 06:02 PM
  4. Error #NUM! - Array Formula
    By Shermaine2010 in forum Excel General
    Replies: 2
    Last Post: 08-21-2011, 03:44 AM
  5. [SOLVED] Error Generated from Array formula
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-14-2005, 09:06 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