The Scenario you have filtered your data in excel, rows hidden and now you want to use SUM to get the total of your filtered row.
Only it does not work.
It includes the rows that are hidden. 😣
Knowing how clever Microsoft Excel is you know there must be a way to stop this from happening… Surely.
Introducing the function SUBTOTAL.
And welcome to the A Boardroom Mum’s blog, a financial consultant bringing you excel tips and hacks and functions that will make your career a lot easier.
We are working smarter not harder.
How to total in Excel when rows are hidden
Example – as you can see we have a few different rows hidden in this example
Rather than using SUM we are going to use SUBTOTAL.
In Cell D31 we are going to type =SUBTOTAL(109,
109 as you can see from the excel drop down list is SUM.
If you do not know the number you need you can type .. and the drop down list will appear for you showing you which number creates the desired function.
Then we are going to type ,D3:D29)
So the total formula is =SUBTOTAL(109,D3:D29)
Hit Enter.
This function has now added up the total net sales in column D excluding the hidden rows.
Perfect 👏
Did you know about this one?
Please save this blog and come back for more tips – it is much appreciated to grow the blog and bring more useful content!
