

Clarify your data ranges with SUMIF
Having already introduced you to IF functions it’s time to show you some more advanced functions that can be used to clarify ranges of data – SUMIF and SUMIFS. SUMIF() The function SUMIF will total only certain cells based on specific criteria. Its syntax is: SUMIF(range, criteria, [sum_range]) Range is the range of cells that will be evaluated by the criteria; sum_range refers to the corresponding cells that are added if the criteria are matched. Sum_range is optional because if left out, the range is both evaluated and added. So let’s start by looking at a simple example containing only range and criteria. The formula in A11 is going to add the values in column A, but only those that are less than 4. This results in 6:



Count occurrences quickly using the COUNTIF formula
So far we have used Excel on whole sets of data, now we focus on how to describe categories within data using the COUNTIF formula. Take a look at the table below.
- The criteria should be entered as the second input and in quotation marks (e.g. “Strategy”).
- The whole cell contents must match the criteria in order to be counted (e.g. if a cell had contained the text ‘Strategy Director’, it would not have been counted).
- The matching is not case sensitive (e.g. a cell containing ‘strategy’ would have been counted).
- If you are counting numbers you can choose not to include the “” marks.
- It is a good habit to fix the ‘range’ reference as absolute using fixed referencing.
- Count blanks by entering “” as the second argument (e.g. COUNTIF(C7:C14,””)).
Alan Gurney is AAT Comment’s Excel tips writer.