Conditional calculations

IF functions for cell ranges

In our previous section we’ve primarily dealt with testing conditions inside of a row, i.e. one or more cells in that row, and returning some kind of a result based on conditions in that row.

While that is useful when we want to add something new to our rows, we will often have to deal with retrieving data based on criteria from whole ranges of cells, sometimes containing thousands of rows and columns.

IF function is not really suitable for that, as we will now illustrate: As you can see, in order to sum incentive paid out to “green” team members, we had to add a whole new column in which those players are filtered with the help of the IF function, and then use the SUM function in the cell F1.

We can return our sum much easier via SUMIFS function: Here, we used a single formula to accomplish our task.

There are many functions available for such operations on data ranges. Here, we will demonstrate modern functions that support both single and multiple criteria, and have among themselves a consistent syntax. You will notice all of them are also ending with “IFS”.

Note however that there are many more methods of doing this, and some of them are still wildly popular, such as an older SUMIF function that supports a single criterion and has a syntax which is not consistent with “IFS” functions.

SUMIFS function

The SUMIFS function adds all of the values that meet one or more criteria.

In the SUMIFS function, sum range is defined first. Criteria range is defined next, and criteria is defined after that: Here, we defined our sum range as cell range E2:E21, and our criteria range as cell range B2:B21. IF a cell in the column B contains text “green”, THEN the corresponding cell in the column E will be added to the sum, ELSE it will be ignored.

Sum range and criteria range can be defined in several ways (incorrect formulas are marked red): As you can see, function in the cell H8 has an incorrect syntax, sum range and criteria range don’t contain the same number of cells, and error is returned: In the cell H10, sum range and criteria range do contain the same number of cells, but error is not returned: In a sense, this is even worse. Sum range and criteria range do contain the same number of cells, but because they are not corresponding, incorrect number is returned.

Had we’ve not known our result ahead of time, we perhaps would not have noticed at all.

Least prone to mistakes way of referencing ranges is shown in cell H6: Here, we are treating the whole columns as ranges.

To reference a column, we just have to click on the column name (letter B or letter E in this case), and there is a minimal chance of mistake. If possible, write your formulas in this way.

What must be true, and always so, is that our criteria range contains the same number of cells as our sum range, and that they are positioned in the same rows.

But nothing is stopping us from defining our criteria range to be the same as our sum range. Let’s say we want to sum incentives of 1 EUR or greater: Here, we defined our sum range as column E, and our criteria range also as column E. IF a cell in column E contains number greater than or equal to 1, THEN that cell will be added to the sum, ELSE it will be ignored.

Empty cells, cell E1 which contains text, cells that contain zero, and finally, cells E10 and E12, are all ignored. This results in a sum of 90,24 EUR, compared to 90,95 EUR total.

The SUMIFS function that adds all of the values that meet multiple criteria is written in the following way: Here, we’ve defined our sum range as column E, but now we have three criteria ranges and three conditions. IF a cell in column B contains text “green”, AND IF player level in column C is equal to 6, AND IF rating in column D is equal to the number in cell H14, i.e. number 1, THEN the corresponding cell in column E will be added to the sum, ELSE it will be ignored.

We have only one cell that matches all of that criteria, cell E2, for an end result of 4,56 EUR.

Notice that the SUMIFS function operates under AND logic when multiple conditions are applied. All of the functions we will demonstrate in this section will operate under AND logic when multiple conditions are applied.

We’ve already shown in our examples how to write criteria, but it is worth reiterating:

• we can reference any cell
• we can enter any number directly in the function
• we can enter text directly in the function, nested inside of quotation marks [“”]
• logical test is nested inside of quotation marks [“”], with comparison operator coming first and a number second.

When dealing with text, we can also use something called partial match.

With an asterisk [*] we can match any sequence of characters.

With a question mark [?] we can match any single character.

For example, we can use an asterisk to return sum of incentive for all teams ending with letter “e”, i.e. blue and white, faster: Here, we defined our sum range as column E, and criteria range as column B. IF a cell in column B contains a text of whatever length with the final letter being “e”, THEN the corresponding cell in column E will be added to the sum, ELSE it will be ignored.

Note that criteria such as “*e*” would return the whole sum of 90,95 EUR, including white and blue team members.

We can also use question mark to return sum of incentive for all users whose user ID contains digit 8 at the 4th place in the ID: Here, we defined our sum range as column E, and criteria range as column A (which contains text). IF a cell in column A has six characters in total, AND contains a digit 8 as the 4th character, THEN the corresponding cell in column E will be added to the sum, ELSE it will be ignored.
You can also combine an asterisk [*] with question mark [?]. Criteria such as “*8??” is valid, and here it would read: IF a cell in column A has four or more characters in total, and contains a digit 8 as the third from the end of the string, THEN the condition is TRUE.

A few more specifics:

Formulas using rows as ranges, such as =SUMIFS(A2:F2;A1:F1;”stars”), or =SUMIFS(2:2;1:1;”stars”), are also legitimate. However, in our example we here don’t have any data where that kind of sum would make sense. You will rarely, if ever, see data structured in a way where using such expressions would make sense.

The SUMIFS formula will not ignore TRUE or FALSE logical values. It will sum TRUE as it were number 1, and FALSE as it were zero.

With tilde [~] we can match an actual question mark [?] or an asterisk [*].

Other “IFS” functions

The AVERAGEIFS function returns the average (arithmetic mean) of all cells that meet one or more criteria.

The MINIFS function returns the minimum value of all cells that meet one or more criteria.

The MAXIFS function returns the maximum value of all cells that meet one or more criteria.

Those functions have the same syntax as the SUMIFS function, and all specifics and caveats also apply.

We can see that in the following example with a general condition: Here, we are retrieving average, minimum and maximum number of stars in column D. Blank and text values in column D are automatically ignored.

Now we will make a quick analysis of incentive paid to each team, while considering only players with incentive paid: Results are rather self-explanatory, average, minimum and maximum per team is returned from the group of players that have received incentive.

What would happen however, if there were no players that received an incentive in some of the teams?

Minimum and maximum would be returned as zero, but the AVERAGEIFS function would return an error!

Implication is that the AVERAGEIFS function is first performing a sum, then a count of cells, and is than dividing those two numbers – and division by zero results with an error.

If we want to avoid errors in such cases, we can help ourselves with the help of IF and ISERROR functions, as follows:

=IF(ISERROR(AVERAGEIFS(E:E;E:E;”>0″;B:B;G10));0;AVERAGEIFS(E:E;E:E;”>0″;B:B;G10)).

The COUNTIFS function returns the number of cells that meet one or more criteria.

It differs from the functions already shown in two crucial ways:

• syntax is a bit different, there is no such thing as “count range”, only one or more criteria range and criteria
• this function does not ignore text, or even, contextually, blank values – be careful while writing your criteria.

We can see those differences in our next example: As you can see by our results, criteria “*” will result in the number of text fields, criteria “” will result in the number of blank cells, and criteria “<>” with the total number of cells in this column minus those containing zero.

We can now produce some actually useful results: Here, in column K we’ve counted number of players that have received incentive, for every team separately. In column L, we’ve calculated the share of players that have received incentive.

Notice how we counted total number of players for every team – our function contains only column B as criteria range and name of the team as criteria.