How many ways can you add cells togther? Part III

The final installment for this summation series...

Some pre-requisite concepts:

Array: An array is conceptually identical to a table. In excel, an array is identified as a range. For example, an array could be "A1:A10", or it could be "A1:C10". In the first array, it's single-column, multi-row, and in the second, it's multi-column, multi-row.

Boolean Statement: In excel, you can can write a formula that includes a Boolean statement. The cell result will be either True, or False (also, 1 or 0). For example. If I wrote in a cell ="Apple"="Apple" the result would be TRUE (or 1). If I wrote in a cell ="Apple"="Orange" the result would be FALSE (or 0).

Using these concepts with the sumproduct formula, you can accomplish any number of logical criteria:

Sumproduct: =sumproduct(array1,array2). This formula will multiply numbers in the same position in the arrays (#1 in array 1 * #1 in array 2) and then add up the results (#1's multiplied + #2's multiplied)

We're going to modify the standard use of this a bit for our purpose. Everything we do will be contained in the first position of the formula.

Lets get to some examples.

First, we'll do a standard sum =SUMPRODUCT(D4:D10) - this is pretty straightforward since there are no other pieces to this function.

Next, lets say we wanted to add up all of the Apples (Just like my last post, but instead of the sumif function, we'll use the sumproduct function)


=SUMPRODUCT(($B$4:$B$10="Apple")*D4:D10)

You can see the D4:D10 reference is still the same, I've just added an argument in the front.

First, we've got $B$4:$B$10). This will, within our formula, create an array that has the values (Apple,Apple,Orange,Apple,Banana,Orange,Kiwi) By adding the ="Apple" piece, I now use the Boolean test for each value:

(Apple=Apple, Apple=Apple, Orange=Apple,...,Kiwi=Apple)etc. This changes my array values to (true,true,false,...,false), or more importantly (1,1,0,...,0).

So now I have (1,1,0,...,0)*(D4:D10). Or, (1,1,0,...,0)*(4,1,3,...,3). Excel will then match these together, so you get (,1*4,1*1,0*3,...,0*3). This is simplified to (4,1,0,...,0), and then further to 12, our answer.

Again, this exercise could have been accomplished with the sumif formula, but the key here is that you can continue to add more criteria simply by including addtional Boolean statements within the function. The sumif formula is limited to a single criteria.

For example, lets say I wanted to add up all the Green Apples.

=SUMPRODUCT((B4:B10="Apple")*(C4:C10="Green")*D4:D10)

This concept has come in quite useful when trying to set up a summary of a dataset that can change over time, or which has come in far more detail than you desire.While it may not be necessary for a small dataset like in this example, when you start to look at dozens, and hundreds, of lines of data, it becomes important to be able to parse through your data.