You need a sum value that should be changed regarding to your filter selections. So, let’s do it!
Please have a look at the following example! The table figures out sales volume and revenue of 5 products in 3 shops.
As usual requirement, filter function would be used (by product and/or shop) to obtain desired sales volume and revenue. However, with SUM function, those values cannot be changed based on filter selections. So, how to make it interactive?
SUBTOTAL function is the key for this issue. Instead of using SUM function, please use SUBTOTAL as below.
Now, let’s see what happens when we use filter to pick up results of Shop ID = S01.
It can be seen sum values of sales volume and revenue are adjusted to show values of Shop S01.
In SUBTOTAL function, the first input here is function_num. There are 11 function numbers here that is explained in the following table.
Let’s trySUBTOTAL function!