SUMIFS

2 posts

Excel SUMPRODUCT Function

The Excel SUMPRODUCT function is a little known function. However it has a very fitting name because it multiplies and then returns the sum of the groups of multiplications, or creates a SUM of the PRODUCT.

Starting With The SUMPRODUCT Function

SUMPRODUCT(array1, [array2], [array3], )

  • array1 = the first array that you want to multiply and add together. This is required
  • array2, array3, … = the next 2 to 255 arrays of numbers to multiply and add together. This is optional

A few notes about the function:

  1. If you have more than one array of numbers and they are not the same size, the function will return the #VALUE! error
  2. If any arrays do not have numbers, they will be treated as a zero by the function
  3. If a single array is passed to the function, it will only sum. It will not multiply.

How to Use It

In the image below, sample data and four examples of the SUMPRODUCT function are shown.

Notice that both cells A3 and A4 have a single array in their functions, while cells A5 and A6 have two arrays in theirs.

sumproduct

What do you think the result will be of each of the different functions?

The first function in cell A3 is using only two cells in the SUMPRODUCT function, specifically A1 and B1. So will it multiply the 1 (cell A1) * 2 (cell B1) and return 2, or will it add the two value and return 3?

What about the function in cell A4 that is using four cells in its SUMPRODUCT function? Will multiply the four cells or add them?

And then, what about the functions in cells A5 and A6? What will they return?

Here you can see what the results are.

sumproduct

Is it what you expected? If not, hopefully the explanation below will help you work through the formulas to see how the results were achieved.

Notice that when the SUMPRODUCT function is only passed one array of numbers, it does not multiply the numbers, but it adds them.

The function in cell A3 is passed the values in cell A1 of 1 and cell B1 of 2, so it is adding the 1 and 2 to equal 3.

The function in cell A4 is passed the values in cells A1 through A4 of 1,2,3 and 4. It is adding the four amounts to equal 10.

However, the functions in both cells A5 and A6 are both being passed two arrays, so the function is multiplying and then summing the product of the multiplication.

But how is the function returning the answers of 11 in cell A5 and 94 in cell A6?

Remember cell A5 has the formula of

=SUMPRODUCT(A1:B1,C1:D1).

You may think that it would multiply A1 * B1, multiply C1 * D1, and then add those two results together. Actually what the function is doing is multiplying A1 * C1, and B1 * D1, and then adding those two results together. This results in (1*3) + (2*4), which equals 11.

And what about the function in cell A6? Notice how it also refers to two arrays, the same as the formula in cell A5. The difference is that the arrays in cell A6 are not just referring to one row of data, but to two rows instead.

So the function in cell A6 returns 96, which is the result of (1*3) + (2*4) + (5*7) + (6*8).

To summarize how the function works, if there is more than one array being passed to it is this:

Take the first value in each array and multiply them. Then do the same for each next value in the arrays. Then add them together.

Now you fully understand how to use the SUMPRODUCT function.

But the really big question is, Where is this function useful?

What to Do With SUMPRODUCT

One SUMPRODUCT use can be for creating summaries of data, totalling based on criteria. For example, if you have data showing sales for different locations and colors and want a total of each location, or a total of each color, or a total of each unique combination of location and color, you can use SUMPRODUCT.

See the image below.

sumproduct

In the above image, notice that there are several entries for each location, each color, and also for the unique combination of location and color. For example, “North” is found on six rows in column A, and “Red” is found on seven rows in column B. The combination of “North” and “Red” on the same row is found on four rows.

Now, in typical Excel fashion, there are several ways that you could create those totals. You could make the data into a table, add totals to it, and then filter based on what you needed. You could use SUMIF or SUMIFS. And there are probably other ways that you do it. Let me know in the comments if you know of other ways to create summaries.

What will be considered here, is how to use SUMPRODUCT to create summaries of the data. Here is a summary of the above data based on location.

sumproduct

Here’s a summary of the data based on color.

sumproduct

And finally, here is a summary of the data based on a combination of location and color.

sumproduct

Each of these three summaries of the data are using the SUMPRODUCT function to filter the data and add the amounts based on the criteria.

Here are the same summaries, but showing the actual SUMPRODUCT function.

sumproduct

sumproduct

sumproduct

Did you notice how the SUMPRODUCT is a bit different. For example, one of the SUMPRODUCT function formulas is:

=SUMPRODUCT((E2:E26)*(A2:A26=H2)).

The cell H2 contains the word “North”. An alternate to the above formula could be

=SUMPRODUCT((E2:E26)*(A2:A26=”North”)).

So you can type in criteria, or use cell references as the criteria.

The first array is the column that you want to sum, the second array is the column that you want to filter on. This formula will filter on one criteria. To filter on more than one criteria, you just need to add more arrays and the filter for each one.

For example, the formula

=SUMPRODUCT((E2:E26)*(A2:A26=”North”)*(B2:B26=”Red”))

will add the amounts in column E that have “North” in column A and “Red” in column B.

Summary

SUMPRODUCT is not a commonly used function, but it can be used to multiple and add arrays of numbers. A probably surprising use of SUMPRODUCT is to create filtered subtotals of numbers.

If you have of other ways to use SUMPRODUCT let me know in the comments below.

 

 

 

Using SUM, SUMIF and SUMIFS in Excel

How many ways can Excel add thee, let me count the ways. Butchered Elizabeth Barrett Browning quotes aside, Excel has many functions for summing numbers. Last time I counted the different SUMs in Excel 2013, there were eight different SUM functions. There are SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2. I had never even heard of the last four functions until I started to write this blog.

I would say, though, that the most common ones are SUM, SUMIF and SUMIFS.

How do you know which one of these three functions to use?

Let’s take a look at each function and see what they do. That should help us decide when to use each function.

Use the Lowly SUM

SUM(number1, number2)

  • number1 = first number, cell, or cell range to sum
  • number2 = second number, cell, or cell range to sum
  • = next number(s) to sum, up to a total of 255

SUM is the most basic of the SUM functions. It adds the numbers entered between the brackets. This can include hard-entered numbers, cells, cell ranges, or a mix of them. Text values are ignored.

The formula =SUM(E1:E5) in the image below will sum the values in cells E1:E5, which for this example results in 19.

sumif
Example of SUM in a Formula

Expand Your Spreadsheets With SUMIF

SUMIF(range, criteria, [sum_range])

  • range = the cells that you want Excel to look in for a match to your criteria. It can be the same range as the range of cells that you are summing, or a different range of cells
  • criteria = what you want to find in the range. Wildcard characters can be include
  • [sum_range ] = If you want to sum a range of cells different from the first range, you enter the sum range here. This is optional

Like SUM, SUMIF also adds the numbers that are entered between the brackets, however you can specify one criteria that has to be matched in the range for it to included in the SUM.

For example, if you have a column with color names and a column of values, you can specify what matching color to sum. The formula =SUMIF(D1:D5,”Blue”,E1:E5) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), which for this example results in 7 (2+5).

sumif
Example of SUMIF in a Formula

Supercharge Your Formulas With SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1,)

  • sum_rangethe cells to sum
  • criteria_range1the cells that you want Excel to look in for a match to your criteria
  • criteria1 = what you want to find in the range
  • = additional range and criteria, up to 127

Like SUMIF, SUMIFS sums a range. However, it allows multiple criteria to be specified.

Notice the difference between SUMIF and SUMIFS in the order of the arguments.

Expanding on the example above, let’s say you have a column with color names, column of values, and another column of items. You can specify what matching color and item to sum.

In this example, we will sum the value of all of the pencils that are blue.

The formula =SUMIFS(E1:E5,D1:D5,”Blue”, F1:F5,”Pencil”) in the image below will sum the values in the second column (E1:E5) that have a matching “Blue” text in the first column (D1:D5), and a matching “Pencil” in the third column (F1:F5), which for this example results in 5, since there is only one row that has both “Blue” and “Pencil”.

sumif
Example of SUMIFS in a Formula

(SUM)MARY

One beneficial and practical use of both SUMIF and SUMIFS is the ability to create a grand total or summary of a large amounts of cells.

Following the examples above, say you have hundreds of rows of data of colors and want a summary of the total value for each color. SUMIF and SUMIFS are excellent functions to simply  create that summary.

Hopefully, now  you have found that SUM, SUMIF and SUMIFS are some really great functions in Excel.

Perhaps you have thought of some creative and interesting ways to use these functions. If so, please comment on how you have used them.