Calcul des ventes par mois d'un produit
Solved
buck.danny
Posted messages
4
Status
Membre
-
elena -
elena -
Bonjour à la communauté CCM,
Je travaille en ce moment dans une entreprise à l'étranger et j'établis un fichier de gestion des stocks prenant en compte ventes et achats.
Voici mon document: https://www.cjoint.com/?0EBqMPnLEp7
Dans la feuille "report" je voudrais faire apparaître les ventes de chaque produit pour chaque mois en faisant référence au tableau des ventes et achats de la feuille "Orders and inventory".
Je pense que c'est un problème très simple pour certains mais je ne parviens pas à le résoudre.
Merci d'avance pour votre précieuse aide
Je travaille en ce moment dans une entreprise à l'étranger et j'établis un fichier de gestion des stocks prenant en compte ventes et achats.
Voici mon document: https://www.cjoint.com/?0EBqMPnLEp7
Dans la feuille "report" je voudrais faire apparaître les ventes de chaque produit pour chaque mois en faisant référence au tableau des ventes et achats de la feuille "Orders and inventory".
Je pense que c'est un problème très simple pour certains mais je ne parviens pas à le résoudre.
Merci d'avance pour votre précieuse aide
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
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
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.
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.
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!
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?