Count total monthly products sold(in Gms.) on google sheet
Hello Guys!
I am struggling with counting total quantity of each product sold in a month.
Example: I want to have a stacked graph where I can view monthly quantity(in Size, gms) of Product A, B, C, D and E sold. Please feel free to make changes to the table accordingly.
Here is the link to the google sheet, I would appreciate your help alot.
Thank you!
You did not provide a link to a Google sheet, you provided an Excel file attachment. We can work on your Excel file but it doesn't guarantee the solution will be compatible with Google Sheets. If you want to ensure a Google Sheets solution, provide a link and allow read access.
Re: Count total monthly products sold(in Gms.) on google sheet
Hey Jeff,
I am new here, so the platform restricts me to post any links but if you could help me with your email I can add you to the G Sheet.
or you can use this /d/1f3I_u3SAEMPUUlBLLICjzmbwShJfX0fsmwFxcvvwX3I/edit#gid=285998313 after the www docs google spreadsheets
https://docs.google.com/spreadsheets...#gid=285998313
Thank you!
Last edited by 6StringJazzer; 09-29-2023 at 05:29 PM. Reason: live link added
Please build out your sample file with the data table that is to be used for the graph.
Also, for us to help in the file you should share it with Editor access, not restricted with Viewer access.
If you are concerned about privacy, then use this form to create a file for anonymous sharing:
https://docs.google.com/forms/d/e/1F...VbWiA/viewform
As a gesture off appreciation, you can click * Add Reputation at the foot of any of the posts of members who helped you reach a solution.
And finally, was your problem solved? if so, please click Thread Tools above the first post of your enquiry, then select [Solved]
[QUOTE=janmorris;5875659]Please build out your sample file with the data table that is to be used for the graph.
Done, apologies it on "comment" and not "editor"
I have updated the file to extract the Products and Totals and inserted a graph.
Let me know if that is what you were looking for.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I am going to leave this up to the OP and thread participants but giving Editor access means that you could have all kinds of collisions if multiple people are trying to develop a solution at the same time.Originally Posted by janmorris
Thanks for the solution man!
Unfortunately, I am trying to get the total quantity sold each month stacked in a graph.
So, for example: Product A size should be multiplied by the quantity(colD*colE) and added for the month. Similarly for Product B, C, D and E and then stacked up to have a graphical view of quantities of these products(A, B, C, D, E) sold on a monthly basis.
Taking this into account, Product A was sold twice in August. Total Quantity of Product A sold in August(colD*colE) = (1000*2)+(1000*1) = 3000.
Sly, Product B = (1000*3) = 3000
Product C = (1000*2) = 2000
Product D = (500*2) = 1000
Product E = 0
Total Quantity of Products sold(A+B+C+D+E) = 9000
Graph view: X Axis - Aug 2023
Y Axis - 9000
Does it make sense?
I added another tab (Chart 2.0) to your spreadsheet. Is this what you're looking for?
Ok, I give in. Where do you get the data for the chart? Is it directly from the source data?
I guess I should have mentioned that, but I was just trying to see if the chart was what the OP wanted.
I used a Pivot Table to create the chart. Sorry for the deception.
Ah, so the source of the data wasn't actually in the file. Fair enough.
I was trying a different way but having a problem with the dates (US vs UK).
Absolutely! Thanks a lot but I have questions.
Now that I have to use the formula in the actual sheet, I have questions:
1. So in the pivot table how did you convert the Order Date to "Year-Month"?
2. And in Values, how did you manage to add formula ='Size (gms)'*Quantity under Quantity Sold?
Thank you again.
In the PT, right click on any of the dates > create pivot date groups > choose the format you want for your grouping.So in the pivot table how did you convert the Order Date to "Year-Month"?
In the Values field of the PT Editor, click the ADD button > Calculated Field and enter the formula. When you reference a column, you have to use the same column header names shown in the source data. If the column name has a space, you have to put it inside single quotes.And in Values, how did you manage to add formula ='Size (gms)'*Quantity under Quantity Sold?
There would only be a conflict if multiple users are editing the same cells.
When multiple people are in the file, this is easilly seen in the top right corner for anonymous animals, and it is simple to create a duplicate of sheets/tabs into which working solutions can be built.
Multiple users editing the same Google Sheets file at the same time is commonplace, and I have never seen any conflict when multiple users are editing different sheets, nor the same sheet when it is seen that another user is active on the same cell.
However, yes, you are right, it is up to the user if they want help in the file or not.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks