• Buffer
  • Sharebar
  • Buffer
jethro's picture

Using the SUBTOTAL Function in Excel

The Subtotal function is commonly used when creating Subtotals on a table with the Data | Subtotals Menu command. However it can be just as easily used by writing the formula and has some powerful uses.
The syntax is SUBTOTAL(function_num,ref1,ref2,...)
The function numbers allow you to use any of the following functions in the subtotal:

(includes hidden values)

(ignores hidden values)



































The most common is of course the SUM function.
When and why should you use SUBTOTAL and not just the SUM function on its own.

The SUBTOTAL function has some powerful abilities that the commonly used SUM function doesn't. For example when summing a list of cells that contains hidden rows or columns the SUM function will include them. However the SUBTOTAL function only operates on visible cells. The values of the hidden cells are excluded from the total. This is especially useful when filtering a list if you want to see the total of the visible (or filtered) cells.
Note by default, if you have a filtered list of values and you double click the sum (sigma) icon on the toolbar after you select a cell below the bottom of the filtered range Excel will not use a SUM formula but automatically use a SUBTOTAL function.

The SUBTOTAL function ignores other subtotals within the range. So if you have several subtotals and a grand total, the grand total formula can be the subtotal of the entire range that includes the subtotals and it will ignore them.

Links to my other Articles about Subtotals including using outlining.



jethro's picture

Back to School

No I am not going back to school. However as part of a blog circle that is the theme for this month.

When the topic was posted i had no idea on what to write. I don’t have many happy memories of my school years. I was bullied heavily for most of it. Skinny and small with glasses, a smart wit and tongue, I didn’t fit in well. Taking French in a largely blue collar school, playing the violin (there were two violinists in the entire school) and being a Christian marked me out as different, and in packs people fear those who are different because they threaten the pack mentality. There was a certain level of thuggery and a pecking order rapidly established based on physical superiority early on in my high school years. I learnt quickly to avoid confrontation wherever possible.

As an academic minded kid I was also not really stretched by my schooling. For most of my life I rarely if ever did homework. I didn’t study for exams but breezed through them with almost no study (except for French).

So holidays were for me blessed relief from the hum drum of school and the constant planning to escape the bullies i had upset on any particular day who were waiting for me down the road. I knew all the back ways home and i could run and bike fast! Back to school was a long time ago and i have little memory of those times.

As an adult now with kids who are home-schooled we also don’t really understand the “back to school” phenomenon. Admittedly we do have holidays but our schooling is so much more relaxed than the normal school families experience. And to be fair my wife manages 99% of that.

So why should I even write a blog post about this topic?

I pondered that for a while. I ruminated various ideas and wrote some posts in my head that never saw the light of day.

And then it dawned on me.

Life is school.

We are always at school. We are always learning. And if we aren’t. then we are missing the learning opportunities being given to us every moment of every day.

As a photographer I have taken well over a million photos. Over the weekend I took 11,000 at an event, most of which were shot in the same location – just of different people. You might think that isn’t a place to learn. And in some ways that is correct. In fact if I didn’t already have the skills and gear to manage that and perform I wouldn’t have been hired to shoot. But as I watch people coming through an “electric shock therapy” obstacle where they are electrocuted as they run through mud I am constantly learning. Technically I am managing the environment, light, gear and people around me to ensure I can get the shot, and I am striving to get each shot better than the last. This is learning. Using the knowledge of the past to improve the future.

And if in micro adjustments I can shoot the 10,001st photo better than the 10,000th one than I have learnt. I have used my past to change my future.

So I cast my mind further out and wonder at how many opportunities we get every day to learn that we just ignore, or don’t even recognise.

  • When my child shows me something she is proud of or just wants to show me, am I learning a little more about her personality and her mind, or am I distracted and ignoring her?
  • When I am driving am I fine-tuning my ability to manage my car and thus almost imperceptibly improve the safety of those in my car and around me?
  • As I craft this article and think about the impact I may be having on others with my words, am I careful about which words I use?

As you read this are you conscious of the learning you can take out of this small article, or are you letting it flow on by? What moments do you have each day that you can learn from but just ignore?

As we learn to live in the present moment and forget the distractions of a future we cannot change by worrying about it, I challenge you to unlock the enormous potential of your past to change your future by learning from your mistakes and improving the way you do everything.

  • Don’t just keep doing things the same way. There is no gain in that. All of mankind’s greatest achievements came from dissatisfaction with the status quo.
  • Do constantly look for improvement – even in the most minute of things, wash the dishes just that little bit more efficiently and save water, time or electricity. The gains may be eternal for someone you can impact with that saved time.

My prayer is that as I can continue to be open to learning that I will change and grow. Back to school is every day.

PS my past school life doesn’t bother me. I made those observations but I don't carry around grudges or bitterness (I hope). Rather I look for ways to positively influence others to not let their past destroy them, or to destroy others.

Finally I’ll leave you with a photo of someone being electrocuted! They even paid for that pleasure.

2015-09-13 Electroshock 2730

This blog post is part of a blog circle.  Our theme for this month is Back to School.  Please come back this week, and read the posts of the others in my circle:

jethro's picture

Where I Live

My morning ritual is to make a coffee at the kitchen counter with a window looking out over our backyard. Usually I am the first up and the house is still, filled only with the sounds of animals other than humans. Peeping of small hatched chickens inside, the raucous morning squawk of my Amazonian parrot, and the clucking of hens right outside the window. Sometimes my wife is up first and out in the yard tending to her chickens, and sometimes my youngest daughter is up (often she rises to the sound of the coffee machine) and then I am bombarded with her inquisitiveness.

i gaze out the backdoor and i see the comforts of home. The familiar clutter of bikes, the BBQ lawnmower, birdcages and chickens.

2015-08-07 Backyard Chickens 002

Zooming in as the chickens come for their food we see each has a personality, a pecking order and they are “so adorable” as the girls say.

2015-08-07 Backyard Chickens 0072015-08-07 Backyard Chickens 035

This is where I live.

My family is my familiar clutter, my girls are my background noise. Yet I need to remember to zoom in. Focus on the individuals amongst the crowd and noise, listen to the request for love an attention and provide that as a husband and father. Sometimes I neglect this. That is not what I want to do. This reminder is good.

And this morning my youngest (the teens are all in dreamland) was excited to show me her new rip stick skills so out in the middle of the street I lie with my camera – giving her attention.

2015-08-07 Mercedes Ripstick 0022015-08-07 Mercedes Ripstick 0012015-08-07 Mercedes Ripstick 003

This is where I live.


This blog is part of a blog circle.  Our theme for this month is Where I Live.  Please come back this week, and read the posts of the others in my circle: 

Live Love Laugh Hope

Captured Bits of Beauty

Pollywog Creek

Breathe Deeply

What Marty Sees

jethro's picture

Critical Security Threat


A critical security threat was announced and an out of sequence patch released yesterday by Microsoft.

It affects ALL pcs running Vista, Windows7, 8, 8.1 and Windows RT.

There is specific news here if you want more http://www.cnet.com/news/microsoft-rushes-emergency-security-fix-for-versions-of-windows/

And the technical stuff is here https://technet.microsoft.com/library/security/MS15-078?f=255&MSPPError=-2147217396#ID0EKIAE

Please patch all your PCs and Servers immediately.