Forum Discussion
Help with functions
I'm working on an inventory. I'm trying to get each row individually from D3 through D763 of number purchased in column D to first multiply by the next column E, from E3 to E763 of base cost for each item and have Excel enter that number into the next column F from F3 to F763, then add all the products from that column F for a grand subtotal of F3 to F763 entered onto line F764. I keep getting errors, or not working at all. I could do the first half of my problem product but couldn't then add F up. I tried sumproduct and it didn't enter the products in column F 3 to 763. it did the math and gave one answer in F3.
Please help! THANK YOU.
Try this formula:
=LET( product, D3:D763 * E3:E763, total, SUM(product), VSTACK(product, total) )- Thank you! I will try these!!
- Hi, thanks. I'm sorry, I'm very basic knowledge of Excel. I get confused on what numbers, go where, what columns and rows if any I highlight for the equation reference boxes and where I want the answers to go. I tried this formula =LET(PRODUCT,D3:D5*E3:E5,TOTAL,SUM(F3),VSTACK(F5,TOTAL)) in a small sample of D3 through F5 sample and it didn't work. I also tried pasting in what you wrote verbatim and it also didn't work. Error messages. If able to, would you please see what mistakes were made and help me correct as needed?
thank youIt may be easier if I share the sample workbook. Please see attached.
The are two sensible ways of doing what you describe. Since the end of 2018 it can be done with a dynamic array formula
= LET( cost, quantity * baseCost, total, SUM(cost), VSTACK(cost, total) )Before that (2007 onward) one would have used an Excel Table for the calculation as well as to hold the source data
= [@quantity] * [@baseCost]and then inserted a Totals row for the Table. Before 2007 you would have had to use relative referencing.
