absolute references

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

Excel Function of the Week - SUMIF

Possibly one of the most powerful easy to use formulas in Excel is the SUMIF formula.

It is very easy to use once you get your head around its syntax and it is extremely useful in all sorts of scenarios.

Here is the Microsoft Excel Explanation.

You use the SUMIF function to sum the values in a range (Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify. For example, suppose that in a column that contains numbers, you want to sum only the values that are larger than 5.

Syntax SUMIF(range, criteria, [sum_range])

I will explain that below more simply.

First let me explain some common scenarios where you might use SUMIF. Hopefully from there you can envisage similar scenarios for your own circumstances.

You have a list of names and values – maybe pays or scores or totals of times you beat them in darts matches or numbers of volunteers in each division or county. When the names appear more than once – e.g. you might have several different staff categories in each store across each state as per the example below.

image

Lets say you want to sum the total number of Part Time Employees in South Australia across all stores.

I am going to show you a summary table below this one that provides all the results for each staff category for each State and then explain how this was done using one simple formula. An understanding of ABSOLUTE and RELATIVE references is important.

jethro's picture

Back To Basics: Using Absolute References in Excel

Excel uses a powerful vector system for managing formulas. Each cell can be linked to many others using formulas. In a simple way of explaining this, 2 cells can be added together in a third cell using a formula that references them. The columns are named alphabetically and the rows numerically.

Thus the first cell is known as A1.

Here are two numbers in cells.

imageimageIt is easy to create a formula that adds 2 cells, E.g., =A1+A2. Entering this formula in cell A3 effectively sums the first 2 cells into the 3rd cell.