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

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)

Originally Posted by Jonmo1

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.

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.

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

Thanks guys.

You're welcome.