Power Functions (under construction)

Before we begin with these functions it will be useful to understand arrays. I've found the easiest way to think of arrays is to think of them as ranges. A1:C10 and F1:J10 would be two arrays (or ranges). That's it, let's get on with the fun stuff.

SUMPRODUCT

Sumproduct
This is very similar to SUMIF/COUNTIF functions, but more powerful. How many times have you wanted to use SUMIF/COUNTIF but use two or more criteria? Well, SUMPRODUCT is the function you want to use.
For example, you have data in the range A1:G1000. Column A contains names, column C contains values, and column G contains location. You want to count all the names where the value is greater than, or equal to, 100 AND the location is "East". You can't do this with COUNTIF, but you can with SUMPRODUCT. The formula would be:

=SUMPRODUCT((C1:C1000>=100)+0,(G1:G1000="East")+0)

For every value >=100 in column C (first array) that also has a corresponding value of "East" in the same row in column G (second array), SUMPRODUCT will return 1. It will then add all the values up (all the 1's). All you need to do is change the arrays (C1:C1000 and G1:G1000) and the criteria (>=100 and "East") to your requirements. You can have up to 30 criteria. One more example, same as above but you want to total the sales revenue for these records (sales revenue is in column B). You're now wanting a SUMIF function.

=SUMPRODUCT((C1:C1000>=100)+0,(G1:G1000="East")+0,(B1:B1000))

The technical stuff:
The syntax for SUMPRODUCT is
SUMPRODUCT(array1,array2,array3, ...). You can use up to 30 arrays. Note that the arrays must have the same dimensions (columns and rows) or it will return an error. Here's what it does:
There are two arrays: (C1:C1000>=100) and (G1:G1000="East"). Both of these arrays equate to a TRUE/FALSE value. In order to coerce a number instead of a BOOLEAN value (TRUE/FALSE), I add "+0" at the end of the array. TRUE values will equate to 1, FALSE values will equate to 0. SUMPRODUCT will then multiply each each component of the array and add it up. So if C1>=100 and G1="East", it will be the same as 1*1 (which is equal to 1). If C2<100 and G2="East, it will be the same as 0*1 (which is equal to 0). If these were the only arrays in my formula (i.e., my formula was =SUMPRODUCT((C1:C2>=100)+0,(G1:G2="East")+0)), the result would be 1 (1+0).

TOP