CPearson.com Excel Newsletter 13-August-2007

You are receiving this email because you subscribed to the CPearson.com Excel Newsletter. If you are receiving this message in error or you wish to change or cancel your subscription, please go to the CPearson Newsletter Management Page or email Chip Pearson.

In this issue, we will take a look at the COUNTIF, SUMIF, and SUMPRODUCT functions. These functions are useful to get summary information about items in a list of data elements.

The COUNTIF function returns the number of times a specified value occurs in a list of data. For example, =COUNTIF(A1:A10,5)
will return the number of values equal to 5 in the range A1:A10. The formula =COUNTIF(A1:A10,">5") will return the number of elements in A1:A10 that are greater than 5. If you want to use a cell reference rather than hard-coding the value in the formula, use a formula like =COUNTIF(A1:A10,">"&C1).This will return the number of items that are greater than the value in C1 in the range A1:A10.

The COUNTIF function does not support a "between" type of comparison, but you can use two COUNTIF functions to accomplish this. For example, =COUNTIF(A1:A10,"<=40")-COUNTIF(A1:A10,"<10")+1 will calculate the number of elements between 10 and 40. You can change the < and <= ( and > and >=) comparison operators to specify "between inclusive" and "between exclusive" (that is, whether the value 10 should be counted as between 10 and 40).

The COUNTIF works with both numbers and text values. For example, =COUNTIF(A1:A10,"apple") will return the number of times the word "apple" (alone, not as part of a longer text value) appears in the range A1:A10.

Excel 2007 has a new function named COUNTIFS that allows you to specify multiple criteria, in a logical AND comparison. For example, the formula =COUNTIFS(A1:A10,"=1",B1:B10,"=2",C1:C10,"=3") will return the number of times the value in column A is 1 AND the value in column B is 2 AND the value in column C is 3.

The SUMIF function allows you to SUM numbers that meet some criteria, possibly in another column. This simplest version is to test the values to be summed. For example, the formula =SUMIF(A1:A10,">5") will return SUM of the numbers in A1:A10 that are greater than 5. The SUMIF function can also use a different column as the test criteria. For example, the formula =SUMIF(A1:A10,"apple",B1:B10) returns the SUM of the values in B1:B10 where the corresponding value in A1:A10 is equal to "apple".

Excel 2007 has a new function named SUMIFS which allows you to specify multiple criteria in a logical AND relation. For example, the formula =SUMIFS(C1:C10,A1:A10,"apple",B1:B10,"tree") returns the SUM of the numbers in C1:C10 where the corresponding value in A1:A10 is "apple" and the corresponding vaue in B1:B10 is "tree".

In its simplest form, SUMPRODUCT multiplies corresponding elements of two arrays and sums those products. For example,
=SUMPRODUCT({1,2,3},{4,5,6})
returns the value 32, which is (1*4)+(2*5)+(3*6) = 32. In this simple form, the SUMPRODUCT function is of limited usefulness. Its real power comes from summing or counting the results of comparisons. It can be used in Excel versions 2003 and earlier to simulate Excel 2007's COUNTIFS and SUMIFS functions.

SUMPRODUCT can be used to aggregate the results of comparisons. Suppose you have a worksheet that lists product names in cells A1:A5, the name of salesman who sold the product in cells B1:B5, and the amount of the sale in cells C1:C5. You can use comparisons to find the number of computers sold by salesman Jones.

This is the same as the Excel 2007 function =COUNTIFS(A1:A5,"Computer",B1:B5,"Jones"). This function makes two comparisons. First, it tests the values in A1:A5 to the word "Computer" (case insensitive: "ABC" = "abc"). This will return an array of Boolean values FALSE or TRUE, indicating whether each cell in A1:A5 is equal to "Computer". The second comparison tests the values in the range B1:B5 to the word "Jones". This, too, will return an array of FALSE or TRUE values, indicating whether each cell in the range B1:B5 is equal to "Jones". Since the SUMPRODUCT function ignores the Boolean values FALSE and TRUE, we use double negation (effectively multiplying by 1, described in the next section) to convert these Boolean values to their numeric equivalents of 0 and 1, respectively. The SUMPRODUCT function multiplies the corresponding elements in each of these arrays and sums the results of each multiplication. The formula evaluates in the following sequence:

=SUMPRODUCT(--(A1:A5="computer"),--(B1:B5="jones"))
=SUMPRODUCT(--{TRUE, TRUE, FALSE, FALSE, TRUE}, --{FALSE, TRUE, FALSE, TRUE, TRUE})
=SUMPRODUCT({1, 1, 0, 0, 1}, {0, 1, 0, 1, 1})
=2

The final result is the count of Computers sold by Jones. This can be extended to calculate the sum of the values of computers sold by Jones by including the price range, C1:C5 in the formula:

This returns the sum of the values in range C1:C5 where the product is "computer" in range A1:A5 and the salesman in range B1:B5 is "Jones". You can include up to 29 comparisons in the SUMPRODUCT function. The function above is the same as Excel 2007's =SUMIFS(C1:C5,A1:A5,"Computer",B1:B5,"Jones") function.

You will notice in the examples that the double negation operator (--) is used. The results of the comparisons operations in the formulas are the Boolean values FALSE or TRUE. The SUMPRODUCT funciton, the SUM function, and the COUNT function, among others, ignore the Boolean values FALSE and TRUE. For example, if you have the values 1, 2, FALSE, TRUE, 5 in cells A1:A5, the formula =COUNT(A1:A5) will return 3, not 5, because COUNT ignores the FALSE and TRUE values.

The single negation operator - is the same as multiplying by -1 and forces the conversion (or "coerces") of the Boolean FALSE or TRUE to its numeric equivalents 0 or 1. The second negation multiplies this result by -1 again. Negative one times negative one is positive one, so the effect the double negation (--) multiplies the value by one, which will retain the sign (positive or negative) of the value, but changes the FALSE and TRUE values to 0 or 1. These values will now be included in the SUMPRODUCT, SUM and COUNT functions.

The Excel functions SUMIF and COUNTIF are simplified versions of the generalization of SUMPRODUCT function. The SUMIF and COUNTIF functions allow only one comparison, while SUMPRODUCT allows up to 29 comparisons. Note that since SUMPRODUCT uses multiplication, which acts in the same manner as a logical AND relationship, you can use SUMPRODUCT to perform AND operations, as in "(product = Computer) AND (salesman = Jones)". For more complex logical operations, you need to use array formulas, which are described in detail on the Array Formulas page.

This concludes the CPearson.com Excel Newsletter for the week of Monday, August 13, 2007.