I recently got a request through LinkedIn from someone I met at a conference who wanted to know how she could do the following..

She wanted to calculate exam results given the following parameters.

” If they do not fail any module but can now have up to a maximum of 3 modules that falls between 35 and <40 they can pass by compensation, my question is how do I get excel to understand it is up to 3 and no more. For example, if they have not failed any modules and have 3 or less modules that come in between the above they would ‘Pass by Compensation’, if they have not failed any modules but have 4 or more between 35 and <40 they fail. But if they fail any module, they fail regardless. ”

This required a combination of IF/And/CountIfs.

You can download the file here…Compensation pass fail question

And in this video I explain how I came up with the formula.

Continue Reading


One of the issues I come across frequently in class are people who have to work with large amounts of data. That often means a Lot of scrolling and selecting of lists of data and I have often wondered how I could make this easier for people. I have found an answer with structural references. In this example, I have decided to use the SUMIFIS function to illustrate this.

The advantages of using structural references are:

  1. Less scrolling and re-selecting..you enter the headings once and that’s it.
  2. If you add/change data in the table, it’s automatically picked up by the function – you don’t have to go back and re-select.
  3. When you look at the formula, it’s easier to follow (both for you and for others)

You can view the video below and the file is here: Structural References practice file



  • Convert your list to a table (click in list, Ctrl and T)
  • You will now see a new Ribbon
  • On the left, rename the table – in the video below I have chosen sales
  • Set up your parameters. In this example I used the following:
Product County Classification
Carrots Galway Services
  •  As always, click where you want the answer to go.
  • Start typing the following: =sumifs(
  • Then type in Sales (the table name) . This will now appear in a dropdown list
  • Double click on Sales, type [
  • You will now see a list of the headings e.g. Product, County, etc
  • Double click on Product
  • Click on I4 (the cell containing Carrots). Press ,
  • Repeat these steps for the County and Classification.
  • Press ) at the end to complete the function.

Continue Reading


In this blog posting, I’m going to show you how to use a feature in Excel to create a large table of values. The feature is called Data Tables and I’m going to use the PMT function with it to show you how to quickly create a large table of  loan repayment values.

Here is the file for you to practise on. 

Here is the video.






Continue Reading