0

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

 

Instructions

  • 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.

Leave a Reply