Sum of cells with formulas already in them

[poptart1108]

Hello,

I am trying to add the value of cells together. These cells already have formulas in them (multiplication). Some of the cells have no value because the data has not been entered yet, they just have the formulas for when the cells other cells are populated the formula is already there.

This is a spreadsheet to track commissions. I have a Vlookup formula in column E to pull the right percentage, then in column F i would enter the cost. Column G has a multiplication fomula that multiplies E and F to get the commission. I also have a column (J) that multiplies F by a split percentage if the rep is splitting it with another salesperson. I want to put a total on the sheet, but it returns #N/A when I use the sum function. Any ideas? Should this be a VBA code instead?

 

[Michael M]

Can you post a small sample of your data ?
It sounds like your LOOKUP function isn't returning a result, and that is carrying through to your sum function.

AND

No, it shouldn't need VBA !!!

 

[poptart1108]

Hi there,

The VLookUp definitely returns values (example =VLOOKUP(C2,Sheet2!A1:B18,2,FALSE)

The vlookup codes are in column E. I have about 300 rows prepoulated with formulas so that when the data is entered is can be calculated.

Column A (sales Rep) Column B (Client) Column C ( Product) Column D (product cost) Column E (commission rate aka vlookup) Column F (premium) Column G (Commission in dollars aka cell is column F*cel in column E) Column H (Percentage to sales rep) Column I (Dollar amount to sales rep aka cell in column H* Cell in column G)

like I said I have 300 cells in column E, G and I pre-populated so that when the data is entered the calculations will happen. I need to be able to get an automatic updated sum of what has been entered thus far. I dont want to have to manually do it or redo the formula everytime i enter a new product/sale

 

[Joe4]

Just make sure that the formulas return a zero if it does not have all the data that it needs yet, so you can perform your sum.
If you need help in doing that, please post a formula that is not returning a numeric value when it does not have all the required data.

Note, if you have VLOOKUP formulas returning "#N/A errors because it does not yet have the required data, use IFERROR around it to return zeroes instead, i.e.

=IFERROR(VLOOKUP(...),0)