Explanation

In this example, the goal is to sum the amounts in the table using the “Start” and “End” values in columns F and G. For convenience, all data is in an Excel Table called data , which means we can use the structured reference data[Amount] to refer to amounts. An easy way to solve this problem is to use the SUMIFS function, as explained below.

SUMIFS function

The SUMIFS function can sum values in ranges based on multiple criteria. The basic function signature for SUMIFS looks like this:

=SUMIFS(sum_range,range1,criteria1,range2,criteria2,...)

In this case, we need to configure SUMIFS to sum values in the Amount column based on two criteria:

  1. Amounts greater than zero (the value in cell F5)
  2. Amounts that are less than or equal to 500 (the value in E5)

Conditions are supplied to SUMIFS as range/criteria pairs, so each condition will be composed of two arguments. To start off, we provide the sum_range to SUMIFS, which contains the values we want to sum:

=SUMIFS(sum_range

Next, add the first condition, which is that amounts need to be greater than zero (the value in cell F5):

=SUMIFS(data[Amount],data[Amount],">"&F5

Here we provide data[Amount] as criteria_range1 , and the “>"&F5 for criteria1 . Notice we need to concatenate the cell reference to the logical operators , and the operators are entered as text*, enclosed in double quotes (”"). Next, we need to add the second condition, for amounts less than or equal to 500 (the value in cell E5):

=SUMIFS(data[Amount],data[Amount],">"&F5,data[Amount],"<="&G5)

As before, we need to concatenate the operator as text to cell C5. When we enter this formula, it returns 1400, the total of all amounts greater than 0 and less than or equal to 500. As the formula is copied down, it returns a new total in each row, based on the values in columns F and G. The structured reference data[Amount] behaves like an absolute reference and does not change. The references to F5 and G5 are relative and change at each new row.

  • Note: SUMIFS is in a group of functions that split criteria into two parts. As a result, the syntax used for operators is different from other functions. See this article for details .

Explanation

In this example, the goal is to sum the quantities in column C by the codes in column E in a case-sensitive manner. The SUMIF function and the SUMIFS function are both good options for counting text values, and both functions support wildcards . However, neither function is case-sensitive, so they can’t be used to solve this problem. A good solution is to use the EXACT function with the SUMPRODUCT function , as explained below.

Unique values

To generate the list of unique values in the range E5:E8, you would normally use the UNIQUE function . However, UNIQUE is not case-sensitive, so it will not work in this situation. This article provides a formula that can extract unique values from a range of cells, taking into account upper and lowercase characters.

EXACT function

The EXACT function has just one purpose: to compare text in a case-sensitive manner. EXACT takes two arguments : text1 and text2. If text1 and text2 match exactly (considering upper and lower case), EXACT returns TRUE. Otherwise, EXACT returns FALSE:

=EXACT("abc","abc") // returns TRUE
=EXACT("abc","ABC") // returns FALSE
=EXACT("abc","Abc") // returns FALSE
=EXACT("ABC","ABC") // returns TRUE

Worksheet example

In the example shown, we have four codes in column E, and we want to sum the values in column C using the codes that appear in column B. In other words, we want to sum the total quantity per code, and this calculation needs to be case-sensitive. For convenience, code (B5:B15) and qty (C5:C15) are named ranges . The formula in E5, copied down, is:

=SUMPRODUCT(--EXACT(E5,code)*qty)

Working from the inside-out, we are using the EXACT function to compare each code in column E with the named range code (B5:B15):

--EXACT(E5,code)

EXACT compares the value in E5 (“ABC”) to all values in B5:B15. Because the range contains 11 cells, EXACT returns 11 results (one for each code) in an array like this:

--{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE}

Each TRUE represents an exact match of “ABC” in B5:B15. Each FALSE represents a non-match. Next, we use a double-negative (–) operation to convert TRUE and FALSE values into 1’s and 0’s. The resulting array looks like this:

{1;0;0;0;0;0;0;1;1;0;1} // 11 results

Note: technically, the double negative (–) is unnecessary in this formula, because multiplying the TRUE and FALSE values by the numeric values in qty will automatically convert TRUE and FALSE values to 1s and 0s. However, the double negative does no harm, and it makes the formula a bit easier to understand, because it signals a Boolean operation .

At this point, we can simplify the formula like this:

=SUMPRODUCT({1;0;0;0;0;0;0;1;1;0;1}*qty)

Next, we need to perform the multiplication step and sum things up.

SUMPRODUCT function

SUMPRODUCT is a versatile function that appears in many formulas because it can handle array operations natively in older versions of Excel. In this case, the array operation is performed by the EXACT function in the previous step. The remaining steps look like this:

=SUMPRODUCT({1;0;0;0;0;0;0;1;1;0;1}*qty)
=SUMPRODUCT({1;0;0;0;0;0;0;1;1;0;1}*{5;1;3;7;6;7;2;2;9;2;3})
=SUMPRODUCT({5;0;0;0;0;0;0;2;9;0;3})
=19

As you can see, the array returned by EXACT behaves like a filter that “cancels out” values in qty that are not associated with the code “ABC”. With just one array to process in the end, SUMPRODUCT sums all numbers in the array and returns the final result: 19. As the formula is copied down, we get the correct sum for each code in column E, taking into account upper and lower case characters.