Tuesday, March 16, 2010

Dry land... hurray! I spent the last 2 days expecting Noah's Ark to come floating by.  My daughter and I checked out the waves coming into Rockport Harbor on Monday. Unbelievable. It is nice to get back to the Midwest though and actually see some sun and dryland.

I've been blogging a bit about other topics but thought I would head back and start chatting about Excel again.   Today, I wanted to mention SumProduct.


SumProduct is a little known but very powerful Excel function.

It multiplies corresponding components in the given arrays, and returns the sum of those products.

So, what does that mean in English?
SUMPRODUCT multiplies the corresponding cells in specified columns and then sums them.
Let's look at the example below:


Typically, if you wanted to know what your sales were for the products shown:
  •  you would multiply C2 times D2
  • then copy it down the column.
  • then you would sum up the column and get 254.

Using Sumproduct will save you steps and time as the SUMPRODUCT formula will automatically multiply the 2 columns' corresponding cells together and then sum it.



Type =SumProduct(C2:C6,D2:D6) and then press CTRL+SHIFT+ENTER

The CTRL+SHIFT+ENTER creates the array and that is why you have the {} around the formula. The {} tells Excel this is an array. (Typing the {} in does not work).

Why does this work?  An array is considered to be a block of related cells that are treated as a group.


The syntax for SUMPRODUCT is: =Sumproduct(array1,array2…)

Sumproduct allows you to have up to 30 array dimensions.

at

2 comments:

  1. Jan Karel PieterseMarch 17, 2010 at 4:37 AM

    The SUMPRODUCT function works just fine as a normal worksheet function; no need for the control+shift+ener.

    ReplyDelete
  2. excel-divaMarch 18, 2010 at 10:16 AM

    Oops- you are right. I got so fixated on the array component that I forgot you don't need the CSE. Thanks Jan.

    ReplyDelete