# SUMPRODUCT–EXCEL’S MOST POWERFUL FUNCTION

Have you ever looked for an Excel function that is extremely powerful, flexible and has multiple uses? The ONE function that will solve most of your daily array of problems? The ONE function that will open doors to advanced Excel skills?

Well! This is it—**SUMPRODUCT.**

SUMPRODUCT can be used to:

- One way lookup with multiple condition(s)
- Conditional sum
- 2-way lookup
- 2-way lookup with multiple conditions(s)
- Sum top or bottom N items
- Text analysis
- Conditional summaries
- Count unique values in a range
- Counting fields with Errors or blank or text or numbers
- Check if a number is ODD or EVEN plus count the same in a range
- Count the number of times a list of values occur in a range
- Monthly/Yearly Analysis

### So what is SUMPRODUCT Function?

Basically, SUMPRODUCT calculates the sum-of-the-product of matching numbers in given arrays i.e from 2 to a maximum of 30 arrays.

Some rules:

- All Arrays should be of equal size otherwise, SUMPRODUCT returns the #VALUE! error value.
- Arrays cannot be a mix of columns and rows.

` `

`= SUMPRODUCT(Array1,Array2,…………,Array30)`

For Example, to calculate total sales from below example, you need to sum the product of Sold Qty*Sales Price*Shipping Cost

Download the SumProduct Worksheet to View All Data

**Lookup, Get-Product, and Sum-the-Product All at one**

You can incorporate condition(s) in SUMPRODUCT to perform conditional sum or count.

For example to generate a summary of the sales per product category you can use SUMPRODUCT with criteria as shown below;

`=SUMPRODUCT(--(Critera range=Criteria), Array1, Array2, Array3)`

**NB:**

the two dashes are added (Double unary) are used to convert an internal array of TRUE/FALSE into 1/0`--(Critera range=Criteria)`

### How It works:

**(Category range=”Furniture”)**will evaluate to**True**in all the cells whose category is Furniture and**False**in all other cells

` {TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE...}`

- To convert this Boolean array to its Numbers Equivalent we use the double negatives (double unary Method)

**{1;1;1;0;1;0;1;1...}**

- SUMPRODUCT then uses 1/0 array to multiply with the Sales qty, price & shipping cost

**NB:**

- You can add more conditions (criteria) on sumproduct to get drilled down summaries.

#### Use ***** where conditions MUST be met for the argument to be evaluated TRUE

*****

For example, breakdown the sales cost of Furniture for the year 2009 only;

`=SUMPRODUCT((Product category=”Furniture”)*(Year=2009), Qty,Price,Shipping Cost)`

#### Use **+ **where EITHER condition is met for the argument to be evaluated TRUE

**+**

For example to calculate the sales for Furniture or Technology in the year 2010 then:

`=SUMPRODUCT(((Category=”furniture”)+(Category=”Technology”))*(Year=2010), qty,Price, shipping)`

# 2 WAY LOOKUP

To Lookup is to use a value in one column/row and find and get a corresponding value from another column/row(s).

As for a 2 Way Lookup, you find and get a value at the **intersection** corresponding to a given row(s) & column(s) values.

For example, you can calculate how many quantities were Ordered by **KIM** on **28-08-2010** using below data.

`=SUMPRODUCT(dates=”28-08-2010”)*(customers=”KIM”)*(ordersData))`

NB: You have to select the whole ordersData (highlighted in Gray) not just the column for KIM orders.

# Counting fields with Errors, odd/even numbers, blank, text, Non-text e.t.c

`=SUMPRODUCT(--ISERROR(array))`

### How it works:

will evaluate to`--ISERROR(array)`

**True**in all the cells containing errors and**False**in all other cells. We use the double unary method to convert an internal array of TRUE/FALSE into 1/0- SUMPRODUCT then adds up the 1/0 array

SUMPRODUCT({1,0,0,1,1,0..})

**NB: **

You can replace **ISERROR** with **ISBLANK**,** ISTEXT**, **ISNUMBER**, **ISNONTEXT** but not with** “ISEVEN”** or “**ISODD”**

**Counting ODD or EVEN numbers use MOD function**

`=SUMPRODUCT(--(MOD(array,2)=1)) counts field with odd numbers`

### NB:

MOD returns a remainder of 1 for odd numbers and 0 for even numbers.

### How it works:

will evaluate to`--(MOD(array,2)=1)`

**True**in all the cells containing odd numbers and**False**in all other cells. We use the double unary method to convert an internal array of TRUE/FALSE into 1/0- SUMPRODUCT then adds up the 1/0 array

SUMPRODUCT({1,0,0,1,1,0..})

# Counting Text Field Based on the Number of their Characters

` `**=SUMPRODUCT(--(LEN(array)=n)) **

`For example to count text field whose length is 4 characters then`

` `**=SUMPRODUCT(--(LEN(array)=4))
**

### How it works:

will evaluate to**--(LEN(array)=4)****True**in all the cells containing text whose length is 4 characters and**False**in all other cells. We use the double unary method to convert an internal array of TRUE/FALSE into 1/0- SUMPRODUCT then adds up the 1/0 array

SUMPRODUCT({1,0,0,1,1,0..}).

# Counting Unique Values in a Range

**=SUMPRODUCT(--(FREQUENCY(range,range)>0)) **

**Formula excludes Blank cells**

# Counting Values Repeated N times

**=SUMPRODUCT(--(FREQUENCY(range,range)=N)) **

**formula excludes Blank cells too**

**Calculating Weighted Average**

**=SUMPRODUCT(array1,array2)/sum(array2) **

**NB: Array2 being the weights**

# Sum the Top* N* Values in a Range Without Duplicates

` `**=SUMPRODUCT(LARGE(SumRange,ROW(INDIRECT("1:N")))) **

### How it Works

For example, calculate the top 3 sales using below data

=SUMPRODUCT(LARGE(F2:F10,ROW(INDIRECT("1:3")))) = 4,162.18

**►ROW(INDIRECT(“1:3”)** evaluates to **{1;2;3} **which forms the ** k **part of LARGE function

►**LARGE(F2:F10,{1;2;3})** fetches the top 3 values **{1999;1163.68;999.5}**

►**SUMPRODUCT({1999;1163.68;999.5})** adds up the items in the array

Or Use below formula

`=SUMPRODUCT(SumRange,--(RANK(SumRange,SumRange)<=N))`

### How it Works

For example, calculate the top 3 sales using below data

=SUMPRODUCT(F2:F10,--(RANK(F2:F10,F2:F10)<=3))=4,162.18

**►RANK(F2:F10,F2:F10)** assigns a ranking number for all the items in the range **{7;3;8;4;1;6;9;5;2}**

►**–(RANK({7;3;8;4;1;6;9;5;2})<=3)** will evaluate to **True** in all the cells whose rank is greater or equal to 3 and **False** in all other cells. We use the double unary method to convert an internal array of TRUE/FALSE into 1/0 i.e . **{0;1;0;0;1;0;0;0;1}**

►**=SUMPRODUCT(F2:F10,{0;1;0;0;1;0;0;0;1})** SUMPRODUCT multplies the two arrays and adds up their products.

**NB: **

To sum the Bottom N Values, Rank the range Ascending

`=SUMPRODUCT(SumRange,--(RANK(SumRange,SumRange,`

`1`

`)<=N))`

# Counting Items Based on Two or More Criteria

For example how many times did we sell furniture in the year 2010? (Download SumProduct Worksheet to view All data)

`=SUMPRODUCT(--(F:F="Furniture"),--(A:A=2010))`

# Find the Last Occurrence of Item in List

When combined with INDEX, SUMPRODUCT can be used to fetch the last occurrence of items in a list**— The List should be arranged descendingly**

For example, fetch the last time Home Office ordered? (Download SumProduct Worksheet to view All data)

`=INDEX(orderdate,SUMPRODUCT(MAX((customer="Home Office")*ROW(customer))),0)`

## Conclusion:

If INDIRECT is Excel’s Most Evil Function because of its Volatility, then SUMPRODUCT is Excel’s Most Powerful Function because of its Versatility.

Learn More On SUMPRODUCT from below links:

http://exceluser.com/formulas/last-item-in-list.htm

http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

http://www.xldynamic.com/source/xld.sumproduct.html#top

http://www.meadinkent.co.uk/xlsumproduct.htm