I am trying to figure out how to get the product with the highest sales per month using Excel. I'm new to excel and it's been a struggle understanding what formula to use.
Please help!
CodePudding user response：
Use a Pivot Table to summarize the data. Use these columns in your fields:
Click the dropdown arrow in the Product column, choose value filters, Top 10. Choose top 1 by sum of sales:
CodePudding user response：
Screenshot(s) refer:
Setup
Sample data (B:M) and unique product ID / unit cost list (O:Q)
Note: Unique product List ony required for Method 3 - see below
Notice col M 'Sales' derived as Store cost / unit cost:
=F4:F29/INDEX(Q4:Q10,MATCH(D4:D29,P4:P10,0))
Only relevant if transactions comprise 1 or more sales of a given product (here, transactions 1001, 1008 comprise 3 pool covers and 4 pool domes resp.). This is covered under method 3 (can also be handled by method 1, albeit only the single product/trans is given in that example).
Methods
- Pivot Table method - can handle 'single' or 'multi' product variations
- Countifs method - only for 'single' product per trans
- Sumifs method - as for pivot table method
1. Pivot Table
(quick but not v. dynamic - i.e. requires refreshing)
a) Select the sample data, and click 'Analyse Data' in Home section of main ribbon:
b) In the 'Ask a question about your data' search bar, type 'Which is the most common 'Product' each month?
c) Select 'insert Pivot table'
Notes:
- You may need to sort row labels if you want this to read chronologically Jan->Apr
- You can try different variations or select one of the suggestions to allow or other summaries, e.g. by $ sales / multi-product variation etc
2. Max number of sales per month (1 sale per transaction) requires Office 365 compatible version of Excel
Obtain unique list of months:
=UNIQUE(B4:B29)
Obtain complete & unique lists for Product IDs each month, for unique lists, determine corresponding count of product IDs (countifs); then join the filtered list corresponding to product ids with max counts using text join -
=LET(x_,TRANSPOSE(SORT(UNIQUE($D$4:$D$29))),y_,TRANSPOSE(COUNTIFS($D$4:$D$29,TRANSPOSE(x_),$B$4:$B$29,S4)),TEXTJOIN(", ",1,FILTER(x_,--ISNUMBER(MATCH(y_,IFERROR(COLUMN(y_)/0,MAX(y_)),0)))))
Option 3 - multi product/transaction
As for option 2 (Office 365, unique month, complete/unique lists, etc.) but now countifs replaced by sumifs, and col M (# sales, cf. set up) utilised:
=LET(x_,TRANSPOSE(SORT(UNIQUE($D$4:$D$29))),y_,TRANSPOSE(SUMIFS($M$4#,$D$4:$D$29,TRANSPOSE(x_),$B$4:$B$29,S4)),TEXTJOIN(", ",1,FILTER(x_,--ISNUMBER(MATCH(y_,IFERROR(COLUMN(y_)/0,MAX(y_)),0)))))
Notes:
- Product codes 9822 (Jan) and 1002 (Feb) outrank other products returned by Option 2 due to multi-product sales (3 and 4 resp.).
- This method is most robust as it can be used to return Product Codes with the highest profit/commission etc. (simply by altering values in col M).
- E.g. setting col M = £Commission yields the following 'highest commission earning Products' each month:
In this case, Products 1002 and 1003 dominate - given their sales volumes and high unit costs (comm. assumed 20% flat across each product otherwise).
General points
Notes: Assuming you intended to insert table as follows:
| A| B| C| D| E| F| G| H| I| J| K |
| ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- | ---- |
| Month | Transaction Number | Product Code | Product | Store Cost | Sale Price | Profit | Commission | First name | Last name | Sale Location |
| Jan| 1001| 9822| Pool Cover| $58.30 | $98.40 | $40.10 | 8.02| Charlie| Barns| NM |
i.e.
| A | B | C | D | E | F | G | H | I | J | K |
|---|---|---|---|---|---|---|---|---|---|---|
| Month | Transaction Number | Product Code | Product | Store Cost | Sale Price | Profit | Commission | First name | Last name | Sale Location |
| Jan | 1001 | 9822 | Pool Cover | $58.30 | $98.40 | $40.10 | 8.02 | Charlie | Barns | NM |
☺