Calculation of monthly sales for a product.
Solved
buck.danny
Posted messages
4
Status
Member
-
elena -
elena -
Hello CCM community,
I am currently working in a company abroad and I am setting up a stock management file taking into account sales and purchases.
Here is my document: https://www.cjoint.com/?0EBqMPnLEp7
In the "report" sheet, I would like to display the sales of each product for each month by referring to the sales and purchases table in the "Orders and inventory" sheet.
I think this is a very simple problem for some, but I can't seem to solve it.
Thank you in advance for your valuable help.
I am currently working in a company abroad and I am setting up a stock management file taking into account sales and purchases.
Here is my document: https://www.cjoint.com/?0EBqMPnLEp7
In the "report" sheet, I would like to display the sales of each product for each month by referring to the sales and purchases table in the "Orders and inventory" sheet.
I think this is a very simple problem for some, but I can't seem to solve it.
Thank you in advance for your valuable help.
2 answers
-
Good evening
a formula to paste in C5 (French version)
and to drag across the entire table, carefully respecting the position of the $ signs
=SUMPRODUCT((MONTH(Orders_and_Inventory!$C$14:$C$20)=ROW()-4)*(Orders_and_Inventory!$E$14:$E$20=Report!C$4)*(Orders_and_Inventory!$F$14:$F$20))
But for it to work, you first need to add the month of May in the list between April and June
otherwise May does not appear and everything gets shifted afterwards
note:
that you can replace ROW()-4 with 1 for January, 2 for February, etc.
that the formula above totals the quantities. To total the amounts, you need to use $I$14:$I$20 in the last item
best regards
--
To err is human, to persist is diabolical-
Thank you very much for your response, Vaucluse!
Indeed, it works perfectly. How do you select one model or another? Because I don’t see this criterion in the formula, and yet it is indeed the model that is selected.
Moreover, when I want to get the data in amounts, I paste $I$14:$I$20 into the last item, but it gives me a #VALUE? error.
Thanks again! -
In the formula, the second item refers to the text in cell C$4 and thus to the title of the column
when you drag the formula down C$4 remains C$4 and if you drag it to the left, C$4 becomes D$4 etc....
crdlmnt
Ps I just tested the formula for sum in June in C10, that is:
=SUMPRODUCT((MONTH(Orders_and_Inventory!$C$14:$C$20)=ROW()-4)*(Orders_and_Inventory!$E$14:$E$20=Report!C$4)*(Orders_and_Inventory!$I$14:$I$20))
and the result is indeed 45000 (which actually corresponds to May given the missing month
Did you not remove the sheet name in the last item? -
-
-
-
Hello,
With a simple Pivot Table: https://www.cjoint.com/c/EEBt67k67ow
Pivot Tables are very powerful. It's worth investing an hour to understand them.
Pivot Table (TCD): http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=109
eric
By continuously trying, we eventually succeed.
So the more it fails, the more chances we have that it works. (the Shadoks)
In addition to saying thank you (yes, it’s done!!!), remember to mark it as resolved. Thank you.
