Using COUNTIFS() and SUMIFS()

Introduction

Sophisticated spreadsheet program makes it very easy to construct various analyses and computations for our personal as well as for professional use. However, the program is a lot more than just an electronic edition of an accountant's green sheet, and even the most basic functions can at times be very challenging. In versions of Excel 2003 and earlier, there was no function to evaluate cells on multi criteria (Walkenbach, p.281). The COUNTIF() and SUMIF() functions evaluate cells using only one criterion. For one common method for adding and counting with more than one criterion was to use the SUM-PRODUCT function. However, in MS-Excel 2007 and 2010, there are a set of matching functions that don't have this restriction. They have the same names as the functions, but, with an S added at the end:

SUMIFS() is the same as SUMIF(), except it accepts multiple conditions.

COUNTIFS() is the same as COUNTIF(), except it accepts multiple conditions.

Discussion

COUNTIFS()

The COUNTIFS() function counts the number of cells within a range that meet multiple criteria. Its syntax is as follows.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

In the above syntax of the formula, criteria_ range1, criteria_range2, and up represent up to 127 ranges (columns of data) in which to evaluate the associated criteria, and criteria 1, criteria2, and up represent 127 criteria in the form of a number, an expression, a cell reference, or text that define which cells will be counted. Criteria can be expressed as a number such as 50 to find a number equal to 50, the expression “>10000” to find an amount greater than 10000, text such as FT to find a text value equal to FT, or the cell reference B4 to find the value equal to the value stored in cell B4. Each cell in a range is counted only if all of the corresponding criteria specified in the COUNTIFS() function are true (Girvin & Jelen, p.26).

For example, in a spreadsheet of MS-Excel, we have a list having numerous products with their respective prices. To calculate the number of products priced over $ 500, we would use COUNTIF() function with the criteria set for prices over $ 500; or, to calculate the number of travel products in the list, we would use the COUNTIF() function setting the criteria. But what if someone wants to use both conditions at once, in other words, he or she wants to zero in on just ...