Excel 2003 Equivalent to Excel 2007's COUNTIFS Function

Wed, 30 Jan 2008 17:10:00 GMT

Microsoft's Excel 2007 has a new function called COUNTIFS which allows you to apply multiple criteria (up to 127) in order to arrive at your count range. Here is how I had it setup, which works great.

COUNTIFS(Employees!$D:$D,1,Employees!$L:$L,"Meets")

This counts all of the employees that have a level of 1 and a performance rating of "Meets". The problem is that this worksheet needs to be compatible to older versions of Excel, particularly 2003. Luckily, there is a pretty easy way to accomplish the same result. I googled and found this thread at www.ureader.com which mentions the use of the SUMPRODUCT function. Here is my backwards compatible version.

SUMPRODUCT(--(Employees!$D:$D=1),--(Employees!$L:$L="Meets"))

So what does this do? Well, the first argument returns an array of TRUE/FALSE values for each cell in the range. True if it equals 1, false if not. Adding "--" to the beginning converts the TRUE values to 1 and the FALSE values to 0. The next argument does the same thing, but for a different range of cells and for the value "Meets". Lastly, the SUMPRODUCT function multiplies each position in the first array with the same position in the second array then adds up the result. Here is an example:

Array 1 = {0;1;1;1;0}
Array 2 = {0;0;1;0;0}
Product Array = {0;0;1;0;0}
Sum Result = 1

The SUMPRODUCT function isn't quite as elegant as the COUNTIFS function, but it gets the job done.

Friday

Fri, 15 Sep 2006 19:54:35 GMT

Well, it's Friday. Thank goodness for that.

RSS feed for InstantSpot site fro

Excel 2003 Equivalent to Excel 2007's COUNTIFS Function

Friday