Cost Calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-2782901%22%20slang%3D%22en-US%22%3ECost%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2782901%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20I%20am%20trying%20to%20make%20an%20Inventory%20spreadsheet%20for%20my%20company%20to%20track%20Inventory%20and%20the%20costs%20of%20supplies.%20For%20the%20cost%20calculation%20I%20am%20confused%20why%20the%20Column%20O%20says%20%23VALUE!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2782901%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2783030%22%20slang%3D%22en-US%22%3ERe%3A%20Cost%20Calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2783030%22%20slang%3D%22en-US%22%3Ejust%20use%20SUM%20or%20%2B%20instead%20of%20SUMPRODUCT.%3CBR%20%2F%3E%3D%20M5%20%2B%20N5%3CBR%20%2F%3E%3DSUM(M5%2CN5)%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi I am trying to make an Inventory spreadsheet for my company to track Inventory and the costs of supplies. For the cost calculation I am confused why the Column O says #VALUE!

4 Replies
just use SUM or + instead of SUMPRODUCT.
= M5 + N5
=SUM(M5,N5)

@Sam_Orouji 

 

I'm going to disagree with @mtarler here. He was going in the right direction, in that the SUMPRODUCT function was not what you really wanted. He used the SUM as what you might have wanted, but if I'm not mistaken, it's really the product, a multiplication, that would give you the desired result.

 

So that would be =M5*N5

 

For what it's worth, Excel's basic math functions are basic math functions. https://exceljet.net/excel-formulas-and-functions

SUMPRODUCT is altogether different from, and much more complicated than, what you needed. https://exceljet.net/excel-functions/excel-sumproduct-function. The time may come when you need it; it's powerful in the right context (I just used it a day ago), but more than overkill for what you wanted to do.

 

See the attached revision of your spreadsheet.

Good catch my friend. My brain was out to lunch. So yes, as you noted, it is same answer as before except use either PRODUCT() or *

@Sam_Orouji 

If you are using Excel tables, why not size them correctly to the data they contain.  The equation using structured references would then fill down to match the price and quantity information.

=[@PricePerUnit]*[@TotalUnits]