- Multiply two columns in excel for mac how to#
- Multiply two columns in excel for mac download#
Multiply two columns in excel for mac download#
You can download the spreadsheet we used for this example here. The result can be see in the field "Total Value of Sales from St Louis.
We have achieved our objective - we have used SUMPRODUCT with a criteria of "City = St Louis". Multiplying the price by the result of this logical formula by the quantity sold for each row means that any row where the City is not St Louis will have a value of zero when SUMPRODUCT adds up the products of all the rows. If the value in the City colum is St Louis, the logical formula returns 1, otherwise it returns 0. This logical formula works by comparing the City value (column B) in each row with the value in D16 (St Louis). It contains the unit price, but it then multiplies the value in the Unit Price column by a logical formula, B5:B15=D16. The first array, D5:D14, is the same, and contains the data for quantity sold. As in the previous example, we have two arrays: Let's look more closely at this formula, =SUMPRODUCT(D5:D14,E5:E14*(B5:B14=D16)). We can expand the SUMPRODUCT function by changing one of the arrays to include a test to see if the City is St Louis, as show here: In other words, we want to use SUMPRODUCT to find the value of sales of Green Widgets, with the additional criterion that we want to include only those sales of Green Widgets made in St Louis. We would like to find the sales of Green Widgets for St Louis. Let's say we have another sales report for Green Widgets which breaks down our sales by city as well as by day, as shown here: In other words, we'll use SUMPRODUCT to find a subtotal of our data based on our criteria. Let's say we want to specify some criteria for the SUMPRODUCT function so it only adds up the sum of some of the rows. It's worth remembering at this point that we can use more than two arrays if we wanted to. Our SUMPRODUCT formula works by multiplying the quantity by the unit price for each row, and then returning the total for all rows in our array.īear in mind that this is a simple example that only uses two arrays - quantity and price - to find the result we want. An array (range of cells) that contains the quantity sold. As you can see, the formula includes two arrays: In this case, we'll enter the SUMPRODUCT formula in a cell below the table:Īs shown here, the formula is =SUMPRODUCT(C5:C14,D5:D14). Without SUMPRODUCT, we would have to add a new column to find the sales for each product, and then add up the results of that column to find total sales. As you can see, we have a range of products on sale at different unit prices. In this example, we'll use the following example where we want to find the total value of sales for Monday: Multiply two columns in excel for mac how to#
Let's look at how to use SUMPRODUCT in a simple example.
As we will see later, you can incorporate a logical test into each array value to emulate the way that SUMIF or SUMIFS works. If any cells have text in them, the SUMPRODUCT functions treats them as containing zeros. You can't have array1 containing 43 cells in a column, and array2 having only 42 cells - Excell will return a #VALUE error. However many arrays you have, they must all be the same shape. In fact, you can have 255 arrays in one SUMPRODUCT formula You only need array1 for the function to work BUT if you only have array1 then SUMPRODUCT works the same way as SUM Where array1 is the data in the first column, array2 is the data in the second column and so on. The syntax of the SUMPRODUCT function is as follows: We'll cover examples of all three scenarios in this lesson. specify multiple criteria for SUMPRODUCT) 
Finding the total sales for all products, but only include those sales for a specific City and Day (i.e. Finding the total sales for all products, but only include those sales for a specific City (i.e. Finding the total sales for all products, where you have Quantity Sold in one column and Price per Unit in another. Scenarios where SUMPRODUCT is useful include: This lesson shows you how to use SUMPRODUCT to do all this, with just one formula. You can also use SUMPRODUCT with one or multiple criteria, whereby SUMPRODUCT will only operate on rows that meet the criteria you define. Without the SUMPRODUCT function, you would have to to create a third column or row in which you write a formula to multiply the Quantity by Price for each entry in your data, and then write another formula to find the sum of all of those numbers. 
Quantity Sold and Price Per Unit) and add the results of each individual calculation together.
The SUMPRODUCT function allows you to multiply two columns or rows of numbers together (e.g.
0 kommentar(er)
