SUMIF or SUMIFS

This chapter is primarily focused on the group of functions that names end in IFS. However, when typing SUMIFS, you will notice a SUMIF function in the intellisense list (Figure 8-2).

The SUMIF function was essentially made redundant when Microsoft released the SUMIFS function with Excel 2007. The SUMIF function can only test one condition, while the SUMIFS can do one or more conditions.

Now, the SUMIF function is heavily used to this day and, therefore, is important to understand, despite its redundant nature.

Notice the key difference is that the SUMIF function begins with the criteria range argument, while SUMIFS begins with the sum range argument due to its handling of multiple conditions.

From this point, the book moves on only with the SUMIFS function, but be mindful that SUMIF can achieve the same examples, but for a single condition only.

Using Text Criteria

For our first example, we will use the SUMIFS function to test a single condition. We will sum the values in the [Total] column for the sales from the East store only.

The text value “East” has been entered directly into the Criteria argument of the SUMIFS function. Table references are used for the [Total] and [Store] columns.

SUMIFS works wonderfully with table data. These references make the formula easier to read and ensure that the sum range and criteria range are of equal size.

However, you probably agree that this is less efficient. It is harder to understand, is not dynamic, and is more prone to user error. The many advantages of using tables were outlined in detail in Chapter 4.

By entering the criteria directly into the Criteria argument, it is a constant. This could be an advantage of its use. But, if we want the user of the spreadsheet to be able to easily change the store being summed, then the formula can reference a cell value, instead of having the criteria “hardcoded” into it.

Cell C3 contains a drop-down list of the different stores, making it easy for a user to interact with it.

Working with Multiple Criteria

Continuing with the previous example, we will use the SUMIFS function with two conditions. We will sum the values in the [Total] column for a specific product and store. The product and store will both be specified by cell values.

The following formula is shown in Figure 8-5. Cell B3 contains the product name criteria and cell C3 contains the store criteria.

=SUMIFS(tblSales[Total],

     tblSales[Product Name],B3,tblSales[Store],C3)

In this example, AND logic is used between the two conditions, and this is the desired response. The SUMIFS function cannot handle OR logic natively. We will see a little trick for OR logic with SUMIFS shortly.

Using Numeric Criteria

Testing numeric criteria with SUMIFS is quite different to how numbers are tested in functions such as IF.

Remember that with SUMIFS, the criteria must be entered as text, a number, or an expression. So, you can enter a number into the criteria, but if you want to test if the number is “>=”, “<”, or some other logical test, then this must be entered as text or written as an expression.

Entering this as text within the double quotation characters (“”) is unexpected as we do not do this when entering similar criteria in IF. But this is a different function, and they all have their little quirks, just like us.

If you want to use a cell value for the number criteria, then the logical characters can be combined with the cell value using an ampersand (&).

In Figure 8-7, cell B3 contains the number of units sold to be tested. In the following formula, the “>=” characters are combined with cell B3 in the formula:

=SUMIFS(tblSales[Total],tblSales[Units Sold],">="&B3)

Sum Values Between Two Dates

The SUMIFS function is perfect for the task of summing values with date conditions.

In this example, we will sum values between two dates. However, the scenario could be to sum values with a single date condition, for instance, the values since a specified date or until a specified date.

Remember, dates in Excel are numbers. So, the technique used is a repetition of the previous example when we tested numeric criteria.

In Figure 8-8, the following SUMIFS formula is entered into cell B6. It sums the values where the corresponding date is greater than or equal to the date in cell B3 and less than or equal to the date in cell D3:

=SUMIFS(tblSales[Total],

    tblSales[Date],">="&B3,tblSales[Date],"<="&D3)

Additional criteria could be added to test for a specific product or store. The SUMIFS function can handle up to 127 conditions.

OR Logic with SUMIFS

The SUMIFS function performs AND logic between its corresponding criteria ranges and criterion. SUMIFS does not support the use of OR logic in its truest form. So, many users would produce a separate SUMIFS for each condition that forms part of the OR expression and add the result of each SUMIFS together.

This works fine. However, there is a trick to perform OR logic with multiple conditions. Let’s see some examples of how to do this.

Continuing to use the data in the [tblSales] table, we will sum only the values in the [Total] column that match multiple specified regions.

In this first example, the store regions are entered directly into the formula as an array of constants (Figure 8-10).

Of course, more than two criteria can be added to the array. You need to separate each criterion with a comma, or a semicolon (;), and enclose them all in the curly braces of the array.

This is great, but I am sure you are thinking – can we refer to cell values instead of typing the text directly into the array?

Well, yes you can. And to take it further, if we format the range of cells as a table, it will create a dynamic criteria range that we can easily add and remove criterion to and from.

In Figure 8-11, the following formula uses the values in a table named [tblStores]. The table is in cells I1:I3. We have the same result as before, but this time the criteria range is dynamic, and the formula is more concise:

=SUM(

SUMIFS(tblSales[Total],tblSales[Store],tblStores)

)

In Figure 8-12, another store region is added to [tblStores]. The table automatically expands, and the SUMIFS function updates the result.

When using a range or an array of constants for OR logic, you can still take advantage of the up to 127 criteria offered by the SUMIFS function (and its friends).

The examples so far have focused on text criteria only. And this is much more likely to be required. However, the same technique can be used with numeric criteria too.

Both date ranges are formatted as tables ([tblDates1] and [tblDates2]), so that they can be easily referenced and expanded or reduced, if required.

Testing numeric values is not as common a requirement as testing multiple text values with OR logic. But cool to know that it is possible.

Using Wildcards with SUMIFS

The SUMIFS function and the others in its group allow the use of wildcard characters. These wildcard characters allow us to perform text matches in our criteria that are similar but not exact.

Let’s look at two examples of using the SUMIFS function with wildcard characters. These examples use the [tblOrders] table on the [Wildcard Characters] worksheet.

The asterisk has been entered as text (enclosed in double quotations) and concatenated to the value in cell B3.

Notice that all three orders that begin with the characters “VO” are summed (313 = 166 + 107 + 40). This is because the asterisk wildcard replaces any number of characters.

This time, only two of the three order numbers that begin with the characters “VO” are summed (273 = 166 + 107). The question marks replace a single character, so in this example, the order number must have exactly four characters following the characters “VO”.

Comparing Two Lists

The COUNTIFS function is fantastic for the task of comparing two lists. If you are trying to find the items that appear in one list but not another, or maybe you want to know the items that do appear in both, COUNTIFS is ideal for these tasks.

Figure 8-19 shows two tables – [tblFirst] and [tblSecond]. We want to identify the names in the first table [tblFirst] that do not appear in the second table [tblSecond].

We will use the COUNTIFS function to count the occurrences of the names in the first table in the second table. This will flag to us if the names are present in the second table or not.

The [Name] column of [tblSecond] is given as the criteria range, and the name in the current row (the @ symbol indicates this row of a table) is given as the criteria.

All results are returned as a 1 or a 0 as there are no duplicated names in this example.

Now, this is serving a purpose, but it would be nice to have a more meaningful output than a bunch of 1s and 0s. So, let’s wrap an IF function around our COUNTIFS to request a different response to be returned.

In Figure 8-21, the following formula uses an IF function to return an empty string (the cell appears empty) if the name is present in [tblSecond] and the string “Missing” if the name is not present:

=IF(COUNTIFS(tblSecond[Name],[@Name]),"","Missing")

Generating Unique Rankings

There are functions in Excel that will rank values in a range in either an ascending or descending order. However, independently they do not create a unique ranking if any of the values are tied.

In Figure 8-22, we have a range of names and their associated score that they achieved. We want to calculate a ranking of each of the scores and then order them by that rank.

Now, you might be thinking, why don’t you just sort the list by the score values? Well, sure we could do this.

However, we would like an automated process that ranks them as the scores are added or changed. Also, by using formulas we could create something more complex and rank by other conditions, if required.

To rank the score values, we will use a function named RANK.EQ, a function that ranks a number relative to the other numbers in a list.

You can see that the scores for Joseph and Garth are tied. Because of this, the RANK.EQ function has duplicated the rank and skipped the following rank (there is no rank of 4).

We need to return a unique rank for each score. This is so that we can use a lookup formula to generate a table with the names and scores ordered by their rank.

The COUNTIFS function is used to count the instances of a score up to the current row. Subtract 1 from this result and add it to the rank to make it a unique value.

Let’s break that down a little more.

Joseph’s score is the first occurrence of the score 84 in the range $C$3:$C3. So, COUNTIFS returns 1. A 1 is subtracted from this result and then added to the rank. This means that the rank does not change.

Garth’s score is the second occurrence of the score 84 in the range $C$3:$C7. So, COUNTIFS returns 2. A 1 is subtracted, leaving 1. And this 1 is added to the rank to become a rank of 4.

The COUNTIFS function has helped us create a unique ranking which the rank functions of Excel could not do alone. Amazing!

To complete this task, we will now order them by their rankings. And for this, we will use the VLOOKUP function that was covered in Chapter 7.

Remember, the VLOOKUP function looks down the first column of a range and returns from a column to its right. Currently, the unique rankings are in the last column. So, we need to change this before we start writing a VLOOKUP.

We can now create a rankings table and use the VLOOKUP function to return the names and scores in order by looking up their rank.

In Figure 8-26, rankings have been typed in column F and then VLOOKUP functions written into columns G and H to return the required information.

Creating Conditional Rankings

Let’s take the rankings example a step further, and instead of using the RANK.EQ function , we can do it all with COUNTIFS. Not only that, but let’s add another condition.

In Figure 8-27, we have a table named [tblRegionalScores] with names and scores that are divided into three different regions.

We want to rank the score values again in a largest first manner, but this time we need three groups of rankings. We want to rank the scores in each region separately.

The COUNTIFS function tests the region and then returns how many scores are larger than the score in that row. 1 is then added to this result.

The 1 is added because if there are 0 scores larger than the current one, then that score is rank 1. If there is 1 score larger than the current one, then that score is rank 2, and so on.

So, the COUNTIFS function can create rankings on its own and can include any extra conditions that may be required.

However, we do have an issue with a duplicate rank again. In Figure 8-28, in the East, Michelle and Darryl both have a rank of 2.

We can use the same technique as we did previously and add a COUNTIFS function to this result to generate a unique ranking.

This is similar to how we added COUNTIFS to the RANK.EQ function before. However, this time we do not need to subtract 1 like earlier, because we replaced the plus 1 from the first COUNTIFS.

You will also notice that a range has been used in the criteria range of the second COUNTIFS. This is to create that ever-expanding range. This is awkward to achieve using a table’s structured reference, so we referenced the grid instead.

From here, we could create separate ranking tables for each region or whatever our end goal may be.

But the focus of this chapter is COUNTIFS, so with this result, we will move on.

Creating a Frequency Distribution

For the final COUNTIFS example, let’s create a frequency distribution table. Excel has a function named FREQUENCY for this purpose, but it is limited when compared to COUNTIFS.

In Figure 8-30, there is a table of scores named [tblScores] and a frequency distribution range with bins defined. The bin ranges can be set up in different ways. In this spreadsheet, the bins are defined with the lowest value, a hyphen or dash, and then the upper bound value of that bin range.

We want to return a count of the scores in each bin. To do this, we will include the LEFT and RIGHT functions that were covered in Chapter 5 to extract the lowest and highest values in each bin range for testing.

The LEFT and RIGHT functions are used to extract the first two and last two characters from the ranges in column E for testing.

This frequency distribution table could be the result, or it could be used as the source for a histogram for a graphical representation.

Calculating the Median

Let’s start with the median average. Figure 8-40 shows a table named [tblScores]. It contains a [Score] column with a couple of outliers (rows 6 and 14).

So, calculating the median may be a better average than the mean in this scenario.

The median is the middle number in a set of numbers. If there is an even set of numbers, MEDIAN will calculate the average of the middle two numbers.

But we want to create a conditional median formula. We want to know the median value for a specific region.

In this formula, the IF function tests the values in the [Region] column against the value in cell E7 and returns an array of all the corresponding values in the [Score] column where the test evaluated to True. This array was used by the MEDIAN function.

If you are using a version of Excel outside of Excel for Microsoft 365 or Excel 2021, you will need to press Ctrl + Shift + Enter to run the formula. In modern versions of Excel, you can just press Enter (dynamic array formulas are discussed in detail in Chapter 10).

Calculating the Mode

The mode is the most common number in a set of numbers. In Excel, there are three mode functions – MODE, MODE.SNGL, and MODE.MULT.

The most used is the MODE function, but this was succeeded back in Excel 2007 by the MODE.SNGL function. MODE is only kept around for compatibility reasons; however, it is still the one most people use to this day.

Let’s focus on the two modern mode functions – MODE.SNGL and MODE.MULT. As you may have suspected, the SNGL stands for single and MULT stands for multiple.

There are three numbers in the [Attendance] column that are equal in being the most frequently occurring. These are 6, 3, and 4. The MODE.SNGL function returns a single value. The 6 is returned as it is the first of the three numbers to occur in the column.

In Figure 8-43, the MODE.MULT function returns all three numbers in the order that they appear in the [Attendance] column.

=MODE.MULT(tblAttendances[Attendance])

If you are using a version of Excel that can natively handle arrays (Excel for Microsoft 365, Excel 2021, or Excel for the Web), then the three results are returned in a spilled array (covered in Chapter 10).

If you are using an older version of Excel, then you would need to select the three cells to store each of the returned numbers before writing the MODE.MULT function. Of course, this would mean that you would have to know that three answers would be returned. This makes the MODE.MULT function almost pointless to be used in this manner in older Excel versions.

Regardless of the version we are using, we may not want to return all three values. The MIN or MAX functions could be wrapped around MODE.MULT to return only the smallest or largest of the returned values.

This is a different behavior to MODE.SNGL, as that function returned the first of the three numbers to appear in the column.

In Figure 8-44, the following formula uses the MIN function to return the smallest of the most frequent numbers:

=MIN(MODE.MULT(tblAttendances[Attendance]))

Now, let’s look at applying a condition to a mode function by including the brilliant IF function.

This combination works in the same way as was discussed with the MEDIAN function.

Identifying Duplicates with Conditional Formatting

Working with duplicates is a common task for Excel users. So, it’s a good thing that Excel has many methods for tackling duplicates. These range from the Remove Duplicates and Advanced Filter features to using Power Query.

Instead of removing the duplicates though, we want to format them to make them easy to see when conducting further tasks.

Excel already has a built-in Conditional Formatting rule to highlight duplicate or unique values in a range. However, it is not as flexible as a custom rule that we can create ourselves with the COUNTIFS function.

Figure 8-49 shows a table named [tblAppointments] that contains some duplicate names. We want to identify the names that occur more than once in the table by formatting both cells of that row in a different color.

The formula counts the occurrences of each name in the range C3:C16 and returns True if the name occurs more than once. Conditional Formatting then applies the chosen format.

Figure 8-51 shows the finished article with both columns of the duplicated names formatted.

Now, by writing our own formulas, we have greater control than the standard built-in rules that come with Excel. So, let’s say that we want to format the names that occur more than twice in the table. This is a more niche scenario of duplicate testing that goes beyond the built-in rules that Excel provides. This rule will be in addition to the previous rule.

Figure 8-52 shows the results with both Conditional Formatting rules applied to the table. There is one name (Martin) that occurs more than twice.

Incidentally, if you only wanted to format the second or third occurrence of the name, and not every instance of the name, edit the second half of the criteria range to have a relative row reference and begin from the first cell of the range. This creates a range that expands each row at a time.

Identifying Unmatched Values Between Two Lists

Earlier in the chapter, we saw the COUNTIFS function being used to compare two lists and flag the names that appeared in the first list but were missing from the second list.

Figure 8-53 shows the two tables that we used for this example. Let’s revisit this task, but this time apply a Conditional Formatting rule to highlight the names in the first list that do not appear in the second list.

Figure 8-55 shows the names that are missing from the second list highlighted by the Conditional Formatting rule.

Highlight Values Larger Than the Median

The Conditional Formatting feature in Excel has built-in rules to highlight values that are above or below the average in the selected range (Figure 8-56).

These rules use the favored mean average just like the AVERAGE function of Excel. However, you may require the use of a different average calculation such as MEDIAN, TRIMMEAN, or MODE.

In this example, we will create a rule to highlight the values that are larger than the median of a specified range.

For this example, we will use the same table of scores that we used earlier in the chapter when we discussed the MEDIAN and conditional median formulas (Figure 8-57).

1. 1.

   Select the range of scores in the table, range C2:C16.

    
2. 2.

   Click Home ➤ Conditional Formatting ➤ Highlight Cells Rules ➤ Greater Than.

    
3. 3.

   Enter the following formula into the Format cells that are GREATER THAN box provided (Figure 8-58):

    

All values in the [Scores] column that are greater than the median value are highlighted (Figure 8-59).

Preventing Duplicate Entries

For the final example, let’s go back to working with duplicate values. This time though, we will use the Data Validation tool with COUNTIFS to prevent duplicate entries in a list.

In Figure 8-60, we have a simple list of countries. We will create a validation rule to prevent the same country from being entered more than once.

This range is formatted as a table named [tblCountries]. Data Validation does not allow the use of a table’s structured references within the validation rules directly. So, we will use range references in our formula, but the rule will still update and automatically expand with the table.

The formula counts the occurrences of the entered country in the table and tests if the result is less than 1. If it is, then it is a valid entry.

In Figure 8-62, the custom error alert is shown when a duplicate country is added to the table.