I am struggling to calculate different data depending on customer and bottle type + amount, cork type + amount & box type + amount in my table.
I want to have a breakdown of customers and their stock requirements.
The formula needs to type the customer name and finds the info from the table and calculate the totals. I have successfully used the SUMIF function to calculate the number of bottles required for example but as soon as one customer has two or more different bottle type etc it gets complex.
Example
1. Joe Blogs, 50cl bottle size, Nocturn bottle type, 6301 cork, 50cl box, total amount 600 (Order 1)
2. Joe Blogs, 50cl bottle size, Nocturn bottle type, 6301 cork, 50cl box, total amount 600 (Order 2)
3. Joe Blogs, 5cl bottle size, Apex bottle type, 6302 cork, 5cl box, total amount 10,000 (Order 3)
3. Mary Jane, 5cl bottle size, Apex bottle type, 6303 cork, 5cl box, total amount 5,000
4. Mary Jane, 70cl bottle size, Nocturn bottle type, 6303 cork, 70cl box, total amount 500
Results
1. Joe Blogs, 50cl bottle size, Nocturn bottle type, 1,200 x 6301 cork, 1,200 x 50cl box (Order 1 & 2 as the bottle size are the same)
2. Joe Blogs, 5cl bottle size, Apex bottle type, 10,000 x 6302 cork, 10,000 x 5cl box, (Order 3)
3. Mary Jane, 5cl bottle size, Apex bottle type, 5,000 x 6303 cork, 5,000 x 5cl box
4. Mary Jane, 70cl bottle size, Nocturn bottle type, 500 x 6303 cork, 500 x 70cl box
I hope this helps.
If it is possible to go one step further and get a total figure of bottles for example which are the same but two different customers then that would be fantastic.
Feel free to reply if you need more details.
Anyone that can help will be much appreciated, many thanks!
Bookmarks