Product Group Arg. Braz. Mex. Peru
Red paint Paint 5 3 2 4
Blue paint Paint 5 6 8 4
Wallpaper Wallpaper 6 5 8 5
Lamps Furniture 4 6 3 1
Chairs Furniture 1 6 4 5
Green paint Paint 1 3 5 6
Sofas Furniture 3 3 5 8
SUM(Paint,Argentina) SUM(Paint,Brazil) SUM(Paint,Mexico) SUM(Paint,Peru)
SUM(Wallpaper,Argentina) SUM(Wallpaper,Brazil) SUM(Wallpaper,Mexico) SUM(Wallpaper,Peru)
SUM(Furniture,Argentina) SUM(Furniture,Brazil) SUM(Furniture,Mexico) SUM(Furniture,Peru)
I am trying to work out VBA code or another way in Excel to find a solution for this problem - I need to be able to sum up the values that I have posted below - so for SUM(Paint,Argentina), this would be the sum of the red paint, blue paint and green paint values under the Argentina heading, that are all categorised into the 'Paint' group. The same follows for all the other categories. I was trying to use 'Defined Names' to group the categories together, and so would then do =Sum(Paint). However this was not allowing me to sum by each country, and the 'Create from Selection' naming tool was not naming all of the values for Paint (blue paint, red paint, etc.) under the paint category.
This is an incredibly simplified version of the data I am actually dealing with - I am doing this for 70 country columns and 250 rows of product categories. But the same principle applies. Many thanks!
Bookmarks