Just like SQL Server, I work with MySQL, PostgreSQL, MariaDB, MongoDB, and Excel. Today we are going to see one very important tip in excel which is about Sum vs SubTotal. Let us see this quick video before we discuss more.
Sum vs SubTotal
Whenever I use Excel I usually end up totaling columns of the excel. I like to use filters with my excel tables as they give me the comfort of “Order By” and “Where clause” of SQL Server. Now the biggest challenge which I face with the filter and SUM function is that whenever I try to use the filter, the SUM function returns incorrect results. This really annoys me as I am a big fan of SQL Server (or any other relational database) and equally loves Excel.
With that said, I am very happy since I found the SUBTOTAL function, it is awesome because it plays along with the filter. I apply multiple filters and the total is always correct. As I do not want the hidden rows to be calculated, I use 109 as the first parameter of the function subtotal which you can see in the video.
Well, That’s it for today. If you like what you see, please do not forget to signup for my youtube channel.
Reference: Pinal Dave (http://blog.SQLAuthority.com)