Sumproduct Function Explained

Excel Sumproduct is one of the most used and versatile function in excel.  It multiplies the corresponding elements of two or more arrays and returns the sum of the products. If used properly, it can save you a lot of your time.

One of the most widely use of this function is to get the weighted average. With the below example, you will understand how to use sumproduct function for calculating weighted average.

Suppose we have to calculate the average selling price of flat panel television of a store. This store sells the TVs of three brands. Here is the summary:

Brand No of TV Sold Selling Price
Sony 52 $1,200
Samsung 38 $980
LG 36 $1,150

So, what will we do? We will multiply the number of TVs sold with selling price for each brand and then divide by the total number of TVs to get the average selling price. That’s right. In this case, the SUMPRODUCT function can be handy to use.

Syntax

SUMPRODUCT(array1,array2,array3, …)

As you can see, the syntax is really simple. You just need to define two or more arrays of numbers and the it will return you with the sum of the products of the corresponding items.

To insert this function, click on the insert function icon. In ‘insert function’ window, you may like to search the Sumproduct or select it from ‘Math & Trig’ category.

insert-function

After you click OK, function argument window appears. Click inside the array1 textbox and make selection to the first array. After this, click inside the array2 textbox and select the second array. Click on OK to get the result.

function-arguments

In the example shown in the figure above, the function does the following:

(1*10 + 2*20 + 3*30 + 4*40 + 5*50)

So, the result is 550.

You can select up to 255 arrays for which you want to multiply and then add the products.

One important thing to note here is that all the arrays must have the same dimensions; otherwise you will get an error. It means if your first array has 5 numbers; second and subsequent arrays must also have 5 numbers for this formula to work.

And if you have a non-numeric value inside the array, the formula will treat it as zero.

Weighted Average with SUMPRODUCT formula

Coming back to our first example of calculating the average selling price of TVs; you can use the following formula.

=SUMPRODUCT(B2:B4, C2:C4)/SUM(B2:B4)

tv-example

Although, for beginners it is easier to use ‘insert function’ window and make selection, you can get your work done faster by directly typing the formula in the cell where you want the result.

For example, if we wish to get our result in cell C6; all we have to do is to start typing sumproduct after a “=” (equals) sign. Microsoft excel will automatically start giving you hints that you need to do next.

excel-hint1

excel-hint2

So, you can directly type the above formula in the cell. Click on enter and you get your result, which in this case is $1119.36.

Arrays need not to be in Columns

In almost every example, you will find that the arrays are arranged in columns. This may give you impression that the arrays must be arranged in columns. In-fact, you can arrange your arrays in both rows and columns and even in multiple rows and columns. Only thing you need to make sure here is that all the arrays must have the same dimension (same number of values).

See the following examples.

Arrays in Rows

sumproduct-rows

 Arrays in Multiple Columns

sumproduct-multiple-columns

This was about the simplest use of this function. Once you are through, we will move on to some more examples.

Sumproduct with Logical Functions

When combined with logical conditions, Sumproduct becomes more powerful. We need to adjust the formula a little to achieve that. I will straightway take you through an example to understand how it works.

Year Brand No of Sales Selling Price
2010 Sony 25 $800
2010 Samsung 30 $900
2010 LG 20 $1,000
2011 Sony 30 $900
2011 Samsung 20 $1,000
2011 LG 15 $750
2012 Sony 25 $900
2012 Samsung 25 $900
2012 LG 30 $800

From this table, we are interested in finding the total selling value of only Sony products in three years.

Type the following formula in the cell where you want to get the result.

=SUMPRODUCT((D2:D10*E2:E10)*(C2:C10=”Sony”))

Press enter and you get the result which is $69500.

There are a few important things to note here:

  • In place of “,” (commas); we are using “*” (asterisks) between the arrays.
  • All the arrays are put inside one bracket.
  • The condition is put under separate bracket and there is an “*” (asterisk) between ranges and condition.
  • You can’t use the insert function here. You need to type manually.
  • You can throw multiple conditions at the same time as per your requirement.

In the above example, the formula checks if the condition (C2:C10=”Sony”) is met. In case of TRUE, excel assigns a value 1 otherwise 0.

Multiple Conditions

If in the example above, we need to know the selling value of only Sony products in year 2011, we can put an additional condition like this.

=SUMPRODUCT((D2:D10*E2:E10)*(C2:C10=”Sony”)*(B2:B10=2011))

Note the pairing of the brackets and use of asterisks. It should not be difficult to understand the logic.

Conclusion

You can use excel sumproduct function to do simple calculations quickly. But when you combine it with conditions, you can do a lot more.

How to use Excel SUMIF Function – Syntax and Examples

Excel SUMIF function is one of the most useful and interesting formula in MS Excel. It is used to calculate the summation of a range based on some criteria. In this tutorial, we are going to take in-depth look at SUMIF formula with easy to follow examples.

I assume here that you know the excel basics and can perform simple operations like sum, average, saving the workbook, etc. You may consider SUMIF function as the combination of SUM and IF function. But before we start our tutorial, here is the quick round up of SUMIF for those who just want to have a quick look at it.

SUMIF Syntax

SUMIF(range, criteria, [sum_range])

Explanation

SUMIF(look into this range, which meets this criteria, [do sum operation in this range])

Here you are providing the list (range) from which you want to select the values meeting the condition (criteria) specified and add the selected values from the same list or other list (sum_range).

Quick Example

=SUMIF(A1:A20, “>10”, B1:B20)

Sum the cells in range B1:B20 if the value in corresponding A1:A20 range is greater than 10.

=SUMIF(A1:A20, “>10”)

Sum the cells in range A1:A20 which are greater than 10.

So, this was a quick round-up. Let’s move on to the detailed tutorial.

Excel SUMIF function

As the name suggests, SUMIF is the combination of two widely used excel functions – SUM and IF. You use SUMIF formula to find the Sum of values which meets a condition specified by you. This formula becomes handy in a situation where you have a large dataset and you are suddenly asked to get the sum of only selected items.

Let’s use an example for easy understanding. Suppose a company sells products “A” and “B” in “East” and “West” zones. The table shows the number of sales for years 2009, 2010 and 2011 divided in zone and product wise.

sumif-example

Now, if you are asked to get the number of sales in east zone, what will you do? If you are not using SUMIF formula, you will likely to sort the data by zone and then use the SUM function to add the sales number for east zone. If the data is too large, it will be time consuming and you are doing extra operations of sorting the data table. Here is the SUMIF function for your rescue!

=SUMIF(B2:B13,”East”,D2:D13)

sumif-formula

With SUMIF, you can quickly find the sum of values for east zone which is 256.

As I already mentioned, the “sum_range” is optional in syntax. If you want to add the cells in the specified in range itself, you should leave this argument blank.

If, in the example above, we are required to get the SUM of sales figures which are less than 40, we can use the following formula.

=SUMIF(D2:D13, “<40″)

And as a result, you get 159. Simple, isn’t it?

SUMIF Function Arguments

  •  range – Required argument. Here you specify the range of cells which you want to evaluate based on some condition. Each of the cells in this range must be a number, name, array or reference containing numbers. Blank cells are ignored.
  • criteria – Required argument. The condition specified by you. This can be a number, reference, text or function. Examples of correct criteria are 10, “>10”, A5, “East”, DAY(Now()), etc. As you can see here, except from numeric values and reference, you need to enclose the criteria in double quotation marks.

You can also use wild card characters – question mark (?) or asterisk (*) in criteria. As you know, a question mark will match any single character while an asterisk will match any sequence of characters.

  • sum-range – Optional argument. The range of cells you want to add. You put this range only if the range you want to add is different from the range of values you want to evaluate. Here sizes of sum-range and range arguments do not necessarily be the same.

Notes

If you want to copy the formula, the cell reference will get changed. In that condition, it is advisable to use absolute references. For creating absolute reference, select the formula in the formula bar and press F4 button. This will convert your formula to absolute reference like the one below and you can copy this to any cell in the worksheet.

absolute-reference-formula

=SUMIF($D$2:$D$13, “<40″)

If you don’t want to use F4, add the dollar ($) sign before the character and digit of the cells name manually (like $D$2 for cell D2).

 Related Excel Formula – COUNTIF & AVERAGEIF

Similar to SUMIF, there is also a COUNTIF function available in excel. As you can guess, this function works the same way as SUMIF. COUNTIF will count the number of cells which match the criteria defined by you. Now that you already know about SUMIF function, you can use the COUNTIF function quite easily. And if you are using Excel 2007 or above, you also have the AVERAGEIF function which finds the average value of the cells which meet the condition.

Only Tutorial you need on Excel IF Function

Excel if function is a logical function of MS excel which is used to fetch one of the two values based on a condition you specify. If the specified condition is met, it will return one value, otherwise it will return another value. If functions are one of the widely used and versatile formulas of MS excel and other spreadsheet programs.

Although Excel if function looks simple and straightforward, it is quite powerful and can save a lot of your time if used properly at the right place. In this article, I will start from the basics and go to the advanced level with nested if function, if formula in arrays, if function with ‘AND’ and ‘OR” and if function combined with vlookup. We will discuss it with real-life examples which will help you to understand it better.

Let’s start with a simple example. Say you have a list of your customers with gender and you want to give away the holiday gifts to all of them. You have separate gifts for men and women – pen sets for men and purse for ladies. If you  use If function, in no time you can get the number of pen sets and ladies purse required. Here, If function will look at a simple condition “Is the person male”? If the answer is “TRUE”, it will return pen set otherwise purse.

Excel If function Syntax

IF(logical_test, [value_if_true], [value_if_false])

To make your life simple in understanding this syntax, the easier way to interpret it is as follows:

IF(Check something, If true do this, Otherwise do that)

For our example, it would be:

IF(Check if the person is male, Return pen set, Return purse)

Explanation of If function formula arguments

  • logical_test: An expression or value which is tested to see whether it is true or false. This field is required. Suppose we want to determine if the given number in cell A2 is negative, the expression will be A2<0. The syntax for determining whether the value is positive or negative should look like this:

=IF(A2<0,”Negative”,”Positive”).

You can use any of the following comparison operators in the expression for logical_test argument:

Comparison operator

Example

= (equal to) A1=B1
> (greater than) A1>B1
< (less than) A1<B1
>= (greater than or equal to) A1>=B1
<= (less than or equal to) A1<=B1
<> (not equal to) A1<>B1

 

  • value_if_true: The value which you want to display, if the logical_test is true. This field is optional. In this example, if the cell value is -5 and we want to display word ‘negative’, we need to put “Negative” in this argument value. Note here that the text is enclosed in colons. This is required for strings used in excel formulae. Since this argument is optional, you can omit it by putting just a comma after logical_test argument like this:

=IF(A2<0,,”Positive”)

In this case, if the logical_test is true, if function will return 0 (zero).

  • Value_if_false: The value which you want to display, if the logical_test is False. Similar to value_if_true argument, this field is optional. Here also, you can omit the argument by putting just a comma after value_if_true argument. As you may guess, if function will return a 0 (zero) in that case.

=IF(A2<0,”Negative”,,)

How to use Excel If function

This paragraph is for starters who are not comfortable with using excel functions. If you know how to use basic functions like SUM, AVERAGE, etc.; you can skip it.

Suppose, as a shop owner, we are interested to give holiday discounts based on the total order value. If the value is less than $100, the discount is 8%. For $100 and above, the discount is 12%. Based on the value entered in a cell, we will use IF function to determine the discount percentage.

  • Open a new excel workbook.
  • In cell B4, we will enter the total order value. Let’s enter 80 here at this time.
  • We want to display the percentage discount in cell C4. Click on cell C4 to select it.
  • Click on “insert item” icon just before the formula bar above worksheet to open the function dialog box.

Insert-If-Function

  • You can also find this “insert item” icon in the Formulas tab.
  • Select the category “logical”.
  • Select “IF” from list of available functions and click OK.
  • The function argument dialog box will appear which will ask you to enter the values for the three arguments i.e. logical_test, value_if_true and value_if_false.

if-function-argument

  • Select the logical_test text box and click on cell B4, then type < (less than sign) followed by 100.
  • In the value_if_true argument, enter 8.
  • In the value_if_false argument, enter 12.
  • Click on OK.
  • Since in this case 80 is less than 100, 8 should appear in cell C4.
  • Change the value in cell B4 to 110, the value in cell C4 will change to 12.
  • If you wish to see the IF formula used, click on cell C4 and see the formula bar. You will find the following formula here: =IF(B4<100,8,12)

Example of omitted argument

Let’s take the same example as above. The only change we are considering here is that if the total order value is less than $100, there will be no discount. So, for the order value less than $100, 0 should appear in the cell C4.

To achieve this functionality, we just need to omit the value_if_true argument. Now, the formula will change as follows:

=IF(B4<100,,12)

Try putting 70 in the B4 cell, the value displayed in C4 cell will be 0 (Zero). If you don’t want 0 to appear in the cell; put “” (empty text) in the value_if_true argument like this.

=IF(B4<100,””,12)

Nested If Functions

Now, as you have fully understood the basics, let’s move on to some advanced topics. Simple IF function is good if you have only one logical test to perform. However, where multiple logical tests are required, we can use nested IF function.

To understand nested IF functions, let’s create an income tax calculator. Suppose, income tax is calculated based on the total income as per following table:

Total Income Income Tax (Percentage of Income)
Less than Rs. 300000 5
Rs. 300000 – Rs. 600000 10
More than Rs. 600000 20

So, how do you determine the income tax percentage? If you think like me, you will first determine if the total income is less than Rs. 300000. If yes, the income tax percentage is 5; otherwise again you see if the total income is less than Rs. 600000. If yes, the income tax percentage is 10; otherwise it is 20, Right?

Great..! Nested IF function works in the similar fashion.

We will use the same worksheet, enter our total income in cell B4 and get the income tax percentage value in cell C4.

First let’s test, if total income is less than Rs. 300000 or not? If True, return 5; otherwise return “Yet to determine”. In cell C4, type the following formula (or you can use function dialog box).

=IF(B4<300000,5,”Yet to determine “)

Now, in place of “Yet to determine”, we will place another IF function to test if the income is less than Rs. 600000? If True, return 10; otherwise return 20.

So, we replace “Yet to determine” with the following formula: IF(B4<600000,10,20)

Our final formula for calculating income tax is a nested IF functions as follows:

=IF(B4<300000,5, IF(B4<600000,10,20))

Perfect. Now, test it with a few values to see what percentage it returns.

Syntax for nested IF function

IF(logical_test1, [value_if_true1], IF( logical_test2, [value_if_true2], [value_if_false2]))

You can nest up to 64 IF functions as value_if_true and value_if_false arguments as per your requirement.

Let’s increase the tax slab in our example and use multiple Nested IF functions.

Total Income Income Tax (Percentage of Income)
Less than Rs. 300000 5
Rs. 300000 – Rs. 600000 10
Rs. 600000 – Rs. 1000000 20
More than Rs. 1000000 30

This time, we will straightway determine the income tax value (not percentage). Try yourself fist and then match with the following formula.

=IF(B4<300000, B4*0.05, IF(B4<600000, B4*0.1, IF(B4<1000000,B4*0.2,B4*0.3)))

IF function with Boolean (AND/OR) function

Combining excel Boolean functions like ‘AND’ and ‘OR’ makes IF function more powerful. Suppose you need to check more than 2 conditions to see if both are true, you can very well use nested IF function. But a better way to do this is to use IF function combined with ‘AND’ function.

How does AND function work?

AND function will return TRUE only if all the parameters are true.

For example,

=AND(10<20, 20<30, 30<50) will return TRUE

But

=AND(10>20, 20<30, 30<50) will return FALSE

IF Function combined with AND Function

In the previous example, let’s assume that for senior citizens (above 60 years old) with total income less than Rs. 500000, there will be no income tax; otherwise it will be 15% flat.

We will enter the total income in cell B4 and age of the person in cell B5 and get the percentage in cell C4. So, the formula in cell C4 is:

=IF(AND(B4<500000, B5>60), 0, 15)

Simple and straightforward, isn’t it?

IF Function combined with OR Function

OR function is equally powerful when used with IF function. As you can understand; OR function will return TRUE if any one or more of the parameters are true.

So, the following formula

=OR(10>20, 20<30, 30<50) will return TRUE

Suppose, in a bus reservation, there is a discount of 20% for kids (less than 10 years) and senior citizens (above 60 years); the formula can be:

=IF(OR(B4<10, B4>50), 20, 0)

The OR function in formula will look into the cell B4 to determine if it less than 10 OR greater than 60. If any of the two is true, it will return TRUE. IF function in turn, will return 20. Pretty simple!

IF function in array

If you pass an array in any of the arguments in IF function, each element of the array is evaluated while executing the formula.

There is a group of functions COUNTIF, AVERAGEIF and SUMIF where the function first checks the array or range that meets the specific criteria and then do calculations like count, average or sum.

Suppose we want to get the average number of sales for 10 days excluding holidays where the sales number is 0. Here, we will use AVERAGEIF function.

averageif

The syntax for this group of functions is as follows:

=COUNTIF(range, criteria)

range:  Required field. One or more cells to count, average or sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are not taken into account.

criteria:  Required field. A number, expression, cell reference, or text string that defines which cells to be taken into account. For example, criteria can be expressed as 20, “>20″, B4, “books”, etc.

VLOOKUP and IF Function

As you know now, for testing multiple conditions, we can use nested IF functions. However, if the number of conditions is huge, we should better use VLOOKUP function. VLOOKUP is much more powerful and can be very handy in complex conditions. Primary and basic function of VLOOKUP is to search any value from a table with data and return a value from a different column from the same row. The VLOOKUP function is a little complex and I have a written a separate article on excel VLOOKUP.

I hope you find this tutorial on Excel IF function useful. If you have any question, suggestion or feedback, please feel free to write in the comments section below.