+ Reply to Thread
Results 1 to 9 of 9

I have a simple =SUM formula, but need to see the value in that cell, not the formula.

  1. #1
    Registered User
    Join Date
    11-11-2015
    Location
    manchester
    MS-Off Ver
    Excel 2010
    Posts
    13

    Lightbulb I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    Ok, so I have a simple =SUM formula =SUM(G2*H2) which works fine.

    On another tab, I have a more confusing formula (SUMPRODUCT), which is looking in the same cell my =SUM formula is in which is causing the formula not to work and show #N/A.

    If I take out the =SUM formula out and replace with just the value (130.58) it works fine.... but I'm creating this spread sheet for somebody else and I need to keep the =sum formula there and have the SUMPRODUCT formula work on the other tab at the same time?

    Any ideas?

    (sheet is attached. Please refer to 'Week 1' F6 and 'Data Input' column 'I'.Nordics - Weekly Costs.xlsx

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

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    Your sumproduct in Week 1 F6 is returning #N/A because column I on the Data Input Sheet contains #N/A errors.
    That column contains errors because it is multiplying values in columns H and G
    The formula in column H is returning #N/A errors from the vlookup function.
    Those are #N/A because the cell it's looking up is blank.

    To resolve, change the formula in Data Input H2 (and filled down) to
    =IFERROR(VLOOKUP(F2,Reference!$F$20:$H$22,3,0),0)

  3. #3
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    15,616

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    First, there is no value is using the SUM function in your formula. Just use

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The SUM formula is not what's causing your problem, though. I'm not following your description of what works fine; there is no value 130.58 anywhere so I don't know what you changed. And it shouldn't matter anyway.

    Here is what I think your problem is. All your formulas in 'Week 1'!F7:F16 refer to data in column G (last argument to SUMPRODUCT) but the formula in F6 (the one that is giving you #N/A) is referring to column I. Is that on purpose? If you use the same formula in F6 as in the cells below it, there is no error.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  4. #4
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    Column I is returning #N/A because the formula is referencing blank cells. This is one way to fix that
    In I2 use
    Please Login or Register  to view this content.
    and drag down.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  5. #5
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    15,616

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    Quote Originally Posted by Jonmo1 View Post
    To resolve, change the formula in Data Input H2 (and filled down) to
    =IFERROR(VLOOKUP(F2,Reference!$F$20:$H$22,3,0),0)
    I agree, this is what is causing the error and I agree with this fix. However, taking into account the whole context I am still not sure the reference to column I is correct to start with. Week 1, row 6, is the only place where that reference is I. Every other row in every other week it's G.

  6. #6
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    I noticed that too Jeff, but you had already covered it in your first post so I didn't say anything.

  7. #7
    Registered User
    Join Date
    11-11-2015
    Location
    manchester
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    My apologies all, the reason only one cell is looking at column G is because it is a new column where all costs are the same currency.

    They all used to look at G but some are Euros, Swedish Krona etc so I had to add a column and change to USD.

    Now I need them all to look at column I but when I tested the first cell in Week 1 it didn't work.

  8. #8
    Registered User
    Join Date
    11-11-2015
    Location
    manchester
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    p.s you were all correct and I used Jonmo1's formula and its all working great.

    Thanks guys.

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

    Re: I have a simple =SUM formula, but need to see the value in that cell, not the formula.

    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)

Tags for this Thread

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