Spy Journal 3.0 Filtering the web for you

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.

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.