Sep 24 2021 10:19 AM
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!
Sep 24 2021 11:04 AM
Sep 24 2021 11:31 AM - edited Sep 24 2021 11:37 AM
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.
Sep 24 2021 11:47 AM
Sep 24 2021 02:22 PM
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]