Now Column E displays whether sales were more or less than $2,000,000,000 for each film.
Whereas IF() is used when the outcome includes only two possibilities – one value appearing if the condition tested is true, the other if false – nested IFs are used to choose between more than two results.
Continuing with the film table example, suppose you want to label all films released prior to 1999 as ‘Pre-99’; those released in 1999 as ‘99’; and those that came after as ‘Post-99’. Then the formula in E2 would look like this:
=IF(D2<1999,”Pre-99″,IF(D2=1999,”99″,IF(D2>1999,”Post-99″)))
A word of caution: nesting to more than three levels can be confusing, and there might be a better way to achieve what you want by possibly using a LOOKUP function instead.
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:
SUMIFS()
The similar function SUMIFS introduced in Excel 2007 has the advantage over SUMIF() of being able to add cells that meet multiple criteria. First though let’s see how it’d be used to accomplish the same as the SUMIF() example above:
Note the syntax difference in that sum_range is required when using the SUMIFS function, and must be the first argument:
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
This final example contains more columns from which we can set multiple criteria for the SUMIFS() function. For example, to count all the apples sold in 2013, we’d type:
=SUMIFS(E2:E11, A2:A11, “2013”, D2:D11, “apples”)
The sum_range is all the values in column E. The first criteria_range is the years in column A, where we’ve specified ‘2013’; and the second is the fruits in column D, where we’ve specified ‘apples’:
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.
If we want to know how many employees there are in each team, we could manually count them if the database was small. If it was a bit bigger, we could sort the rows by team name (with Basic Sorting & Filtering), which would make counting manually easier. But if the database was very large, or it was subject to periodic updates, the manual method would be too time-consuming.
The COUNTIF() formula counts cells just as COUNT() does, but only if their contents are something specific, using a range and a single criterion that you indicate. For example, you can count all the cells that start with a certain letter, or that contain a number that is larger or smaller than a number you specify.
In the spreadsheet shown above, specifying
=COUNTIF(B2:B12,”Strategy”)
will take the range B2:B12 (the first input) and count how many cells contain exactly the entry ‘Strategy’. The result will be 3.
Excel calls the set of cells being counted over (B2:B12 in this example) the ‘range’, and the text that selects which of those cells will be counted (in this example the contents “Strategy”) the ‘criteria’.
There are a few things to note in using COUNTIF() in this way:
- 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.
