Excel SUMPRODUCT Function

 

Introduction

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS but with more flexibility. Other functions can easily be used inside SUMPRODUCT to extend functionality even further.

Purpose

Multiply, then sum arrays.

Return value

The result of multiplied and summed arrays.

Syntax

=SUMPRODUCT (array1, [array2], ...)


Arguments

·       array1 - The first array or range to multiply then add.

·       array2 - [optional] The second array or range to multiply then add.

Useful notes

The SUMPRODUCT function works with arrays but it doesn't require the normal array syntax (Ctrl + Shift + Enter) to enter. The purpose of the SUMPRODUCT function is to multiply then sum arrays. If only one array is supplied SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

When you first encounter SUMPRODUCT it may seem boring, complex and even pointless. But SUMPRODUCT is an amazingly versatile function with many uses. Because it will handle arrays gracefully and without complaint, you can use it to process ranges of cells in clever, elegant ways.

SUMPRODUCT for conditional sums and counts

Assume you have some order data in with State in column B and Sales in column C:


Using SUMPRODUCT you can count total sales for Panchkula with this formula:

=SUMPRODUCT(--(B3:B6="Panchkula"))


And you can sum of total sales also:


Now, you can sum of total sales for Panchkula with this formula:

=SUMPRODUCT(--(B3:B6="Panchkula"),C3:C6)


Note: Don't be confused by the double-negative. This is a common trick used in more advanced Excel formulas to coerce TRUE and FALSE values into 1's and 0's.

For the sum example above, here is a virtual representation of the two arrays as first processed by SUMPRODUCT:

Each array has 4 items. The first array contains the TRUE / FALSE values that result from the expression B3:B6="Panchkula" and the second array is the contents of C3:C6. Each item in the first array will be multiplied by the corresponding item in the second array. However, in the current state, the result of SUMPRODUCT will be zero because the TRUE and FALSE values will be treated as zero. We need the items in array1 to be numeric, they need to be "coerced" into ones and zeroes. This is where the double-negative comes in.

By using the double negative -- (double unary, for you technical types) we are able to coerce the TRUE/FALSE into the numeric values one and zero as shown in the virtual representation below. The last column "Product" represents the result of multiplying the two arrays together. The summed result 71 is the value that SUMPRODUCT returns.



Using the curly brace syntax for arrays, the example looks like this after coercion:

=SUMPRODUCT({0,1,0,1},{25,50,75,21})

and like this after multiplication:

=SUMPRODUCT({0,50,0,21})

SUMPRODUCT with other functions

SUMPRODUCT can use other functions directly. You might see SUMPRODUCT used with the LEN function to count total characters in a range or with functions like

ISBLANK, ISTEXT etc. These are not normally array functions but when they are given a range, they create a "result array". Because SUMPRODUCT is built to work with arrays, it able to perform calculations on the arrays directly. This can be a good way to save space in a worksheet by eliminating the need for a "helper" column.

For example, assume you have 4 different text values in B3:B6 and you want to count the total characters for all 4 values. You could add a helper column in column C that uses this formula: LEN(B3) to calculate the characters in each cell. Then you could use SUM to add up all 4 numbers. However, using SUMPRODUCT you can write a formula like this:

=SUMPRODUCT(LEN(B3:B6))


When used with a range like B3:B6, LEN will return an array of 4 values. Then SUMPRODUCT will simply sum all values and return the result with no helper column needed.

Notes:

1.   SUMPRODUCT treats non-numeric items in arrays as zeros.

2.   Array arguments must be the same size. Otherwise, SUMPRODUCT will generate a #VALUE! error value.

3.   Logical tests inside arrays will create TRUE and FALSE values. In most cases, you'll want to coerce these to 1's and 0's.

4.   SUMPRODUCT can often use the result of other functions directly.


You can also see the video regarding "Sumproduct"



Recommended Articles






If Function




















.


Comments

Post a Comment

Popular posts from this blog

TDS on Salary – Entry in Tally

Image
  Introduction TDS is Tax Deducted at Source. TDS is the amount of tax which is deducted by the employer from the taxpayer and is deposited to the Income Tax Department on behalf of him/her. The TDS rates are set on the basis of the age and income of different individuals. TDS or Tax Deducted at Source is a specific amount that is reduced when a certain payment like salary, commission, rent, interest, professional fees etc. is made. The person who makes the payment deducts tax at the source while the person who receives a payment/income has the liability to pay tax. It lowers tax evasion because the tax will be collected at the time of making a payment. Note: TDS is nothing but advance payment of income tax. When the TDS applied When you get your salary, TDS is deducted from your salary. Let’s for simplicity assume that the TDS on your salary is ₹20,000 and you are applying for the refund of the TDS because your income i.e. your salary is under the base limit for income tax wh
Read more

Post Dated Cheque (PDC) Management

Image
  Introduction Post Dated Cheque is an essential part of today’s business world. For a better financial planning and fund management PDC cheque is a must tool to survive in a tight financial conditions of a competitive world. A cheque in which a future date is written on its face is called Post Dated Cheque or popularly known as PDC. PDC can be issued to your suppliers and can be received from your customers. Normally Post-dated cheques are presented on the date of Cheque and booked or accounted on the same date. If you have a large number of PDC to manage then it is annoying for accountant to keep track of cheques. Tally ERP 9 take charge of the PDC Management for you and let you do a tension free accounting. You can enter post-dated cheques into receipt voucher, payment voucher and contra vouchers. Benefits of Post-dated Cheque Transactions 1.    You can enter the cheque into tally immediately after receiving or issuing it. 2.    You can mark a cheque as post-dated which wi
Read more

Personal Accounts in Tally

Image
 Introduction You can easily maintain personal accounts in Tally. To maintain personal accounts in Tally is very easy and simple. It is much faster than maintaining accounts for a company. The reason is that personal accounts are comparatively very small and involve only a few amounts of entries. Now compare that to a fully-fledged company which is selling products and includes all kinds of accounting entries and on the top of that, there is GST accounting entries too. But when you maintain personal accounts in Tally, there is generally no GST involved and you need to just focus on your balance sheet. So, let’s get started step by step. Maintain Personal Accounts in Tally When you have to maintain personal accounts in Tally, you should mainly focus on balance sheet. There is a specific reason for that. In personal account, there is no business account involved because it is your personal accounts. Therefore, there is NO need for a Profit and Loss Account. If you have a business, It
Read more