Tag: visual basic excel training

Posted on

Using SumIF to add up specified values in Excel

Part of the process of learning about Excel is overcoming the fear of new terminology, especially when it is linked to functions. Today’s term is SumIF.  I use Autosum all the time, so why would I need to use SumIF?

Well, Autosum adds up every cell that I select in a row or column. Great for totalling invoice totals, or expenditure for a month, or adding up items in inventory lists.

But what if I only want to add up items over a specific value?  For example, if I have the authority to make payments up to the value of £250 within a list of expenditure – could I add up the items that were up to and including £250 in value?

I can do this if I use SumIF – as it is designed to add up a range of cells only when the criteria I specify is met.

The format I need to use is =SUMIF(range, criteria)

In my example, the SumIF formula I will use is =SUMIF(B2:B8,”<=250″), as my range is B2 to B8, and I want to see invoices authorised from less than or equal to the value of £250).

SumIF-visual-basic-excel-training
The formula bar shows the SUMIF formula, with the range and criteria specified. The result is shown in cell E2.

This is a simple example where SumIF can calculate the totals authorised by the admin department and those authorised by the Manager.

SumIF can do far more than this, and it is worth exploring the options it gives you.

There are lots of uses of the SUMIF function that can really transform your Excel worksheets.    From Introduction to Advanced, PowerPivot to Visual Basic Excel Training, our courses cover the range skills that can boost your performance at work.
Take a look at what Excel can do for you at https://www.stl-training.co.uk/microsoft/excel-training-london.php 

Posted on

What are contextual tabs in Excel?

I was on an Excel Intermediate course last week, and I noticed that when I was working with a chart, specific tools appeared.  When I clicked out of my chart, the tools would disappear…What is going on Excel?  Why are you doing that?  How do I get them back?  (Mild panic followed…”have I broken it?”)

Fortunately, my calm and understanding trainer explained that these disappearing tools are called contextual tabs.  And no, Excel doesn’t hide them to induce Excel-panic, it is actually being pretty smart.

Contextual tabs appear when you are creating or editing an object like a chart, or table or even a diagram in Excel.  The contextual tabs give you the options and tools you need to work with these objects and then when you click out of the object the tools are tidied away.  To get them back, you click on the object, and the tab appears again.  It keeps your working space uncluttered.

contextual-tab-visual-basic-excel-training
The green tab is a contextual tab, that appears when I’m working with my chart.

In the above screenshot, I’ve put a chart into my document, and you can see a green highlighted tab – this is the contextual tab which I can use to amend my chart.  When I click out of my chart, and back into my spreadsheet, the contextual tab disappears.

no-contextual-tab-visual-basic-excel-training
When I click out of my chart, the contextual tab disappears…look! the green highlighted tab isn’t there…

See if you can spot the difference!

Getting used to what Excel does, and when, is really essential – I can get more out of it if I understand why it does these strange things. Contextual tabs stop winding me up when I know why they appear, and why they don’t.

When I’m ready, after a bit more practice, I’m going to try visual basic Excel training – but for the next few weeks, I’m going to keep experimenting with what I’ve learned so far.  If you want to lose the Excel-panic or just get more out of the program, take a look at what the courses cover on https://www.stl-training.co.uk/excel-vba-2010-training-course.php.