Interactive Sum in Excel

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.

screen shot 2019-01-19 at 23.19.48

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.

screen shot 2019-01-19 at 23.26.36

Now, let’s see what happens when we use filter to pick up results of Shop ID = S01.

screen shot 2019-01-19 at 23.28.05

It can be seen sum values of sales volume and revenue are adjusted to show values of Shop S01.

Note:

In SUBTOTAL function, the first input here is function_num. There are 11 function numbers here that is explained in the following table.

screen shot 2019-01-19 at 23.33.38

Let’s trySUBTOTAL function!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s