What will we achieve today?
Today I have a data set that shows the revenue share of each product category in each region (see below). I want to easily get the subtotal for any combination of some categories and some regions.
For example: I want to know the total revenue share of Accupril, Alavert in China, Japan and India
The traditional way will be that find the corresponding cells and sum them up. And it will tell us that the total is 1.097%. This is still doable for small subset. But it will become complicated when it comes to bigger subsets.
Step by Step
1. Create the calculator sheet: product list, country list, a button link to the calculation macro, and assign a cell to show the result
2. The VBA Code
- highlight the cell when it's selected (single click) and dehighlight when it's unselected
2. make the list area locked while the rest unlocked, and in the code, modify it to only works on locked cells
3. Add a function to select and unselect all the products/countries by clicking on the corresponding cell
4. Write the code to calculate the selected subset and show it in the assigned cell
5. assign the macro to the button
6. Done. Now you can easily get the sum-up of any subset you want to check.
Still not clear? Check the video here:
0 Comments