+ Reply to Thread
Results 1 to 5 of 5

formula copied or updated does now work! (sum if in this case)

  1. #1
    Registered User
    Join Date
    01-08-2004
    Location
    Portugal
    Posts
    2

    formula copied or updated does now work! (sum if in this case)

    Hi Guys I have a really strange problem on excel2010

    I have the following formula for instance in cell F26
    =SUM(IF($C$8:$C$16=$C26,(((F$8:F$16)*($E$8:$E$16)*8*F$4)),0))

    which returns the right numeric value

    when I copy this formula to the cell F27 it works great. Then I realized that in formula I actually wanted to change the F$4 to F4 now the formula does not return any result. IT returns the infamous #VALUE!

    If I go back to F26 and also change the formula I get #VALUE!

    How is this possible ??? I'm not changing anything in the calculation

    Please help!

    thanks joe

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: formula copied or updated does now work! (sum if in this case)

    Hi,

    This formula looks like it needs to be array-entered: are you sure that you are confirming it as an array formula after each time that you edit it? Do you know how to do this?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

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

    Re: formula copied or updated does now work! (sum if in this case)

    That's an array formula that needs to be entered ina special way...

    When you edit the formula, press CTRL + SHIFT + ENTER...Not just enter.

  4. #4
    Registered User
    Join Date
    01-08-2004
    Location
    Portugal
    Posts
    2

    Re: formula copied or updated does now work! (sum if in this case)

    AMAZING!!!! CTRL + SHIFT + ENTER solved the issue. I was actually using a workbook provided to me by a colleague and had to make an ammendment.

    Thanks to both!

    FYI: I joined this forum in 2004 and only now posted because generally I've found my solution from other guys.

    Hope this helps someone also

    once again tahnks!

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

    Re: formula copied or updated does now work! (sum if in this case)

    You're welcome.

+ 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. Copied VBA code won't work.
    By ABBOV in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-12-2010, 05:48 AM
  2. Which formula would work best in this case?
    By LIZCONSTRUCTION in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-07-2010, 05:49 PM
  3. Column of Data to be copied and Continusle updated
    By cakonopka in forum Excel General
    Replies: 4
    Last Post: 05-17-2007, 06:15 PM
  4. Creating an updated work completed sheet?
    By nbaj2k in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-25-2006, 02:20 PM
  5. [SOLVED] Change the text from lower case to upper case in an Excel work boo
    By dave01968 in forum Excel General
    Replies: 2
    Last Post: 12-09-2005, 05: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