Table

4 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.

 

 

 

Harness the (Power) of Excel VLOOKUP

Excel VLOOKUP is quite possibly one of the most popular and yet misunderstood Excel functions. It is a powerful function that can be used to simplify and automate so many things in Excel.

As an Excel user advances in their skills, VLOOKUP typically becomes one of the next powerful tools used.

See how to use it, as any tool becomes more useful as the user’s knowledge and skill in manipulating it increases.

Start With the Basics

Basically, what VLOOKUP does is look in a column of a table of data for a value that you tell it to find. It then returns whatever value is in the same row of that table of data, but from a column that you tell it to look in.

The one restriction with VLOOKUP is that you can’t return a value that is to the left of the column that you are looking in. You can only return values in columns to the right.

Examine the Excel VLOOKUP Function

Here’s the function:

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

  • lookup_value = the value you want to find in the table
  • table_array = the table of data that you want to look in
  • col_index_num = the column in the table of data that you want the value returned to you
  • range_lookup = logical value. If you want to find the closest match (TRUE) or an exact match (FALSE)

See How Excel VLOOKUP Is Used

The image below is a table that will be used for the VLOOKUP function. The store number of ‘1184’ will be looked up in the table to find that store’s phone number. It’s in cell A4 and highlighted in a red box.

vlookup-table-highlighted-value
Table of Data for VLOOUP

This is the formula that will be used to get the phone number for that store number using VLOOKUP:

=VLOOKUP(1184,A2:C5,2,FALSE)

The result of that formula will be (555) 555 6492, which is the value in cell B4.

Let’s break the formula down into plain English:

“Using the data in cells A2 through C5, find the exact match of the value ‘1184’ in left-most column. Once you find ‘1184’ in that column (which is always column 1), show the value in column 2 of that same row.”

The Real Power of VLOOKUP

In the example above, the value of “1184” was hard-entered into the formula. That was just done for the sample.

The real power of VLOOKUP is that you can pass a cell value, a formula result, or the result of a function to VLOOKUP.

Imagine that you have a table of data with those store numbers. It has hundreds, thousands, or even more rows. You want to know the address and phone number for each of those rows.

Just like this sample that has only a few rows:

data-needing-excel-vlookup
Sample Data Needing VLOOKUPs

All that you need to do is add two columns to the data – one for the address, and one for the phone number.

Now, add the VLOOKUP formula to those two columns, and instead of hard-entering the value that you want to look up, enter the cell address that has the store number.

This is what it will look like:

excel-vlookup-formula
The VLOOKUP Formulas

Copy that formula down in each column, using this technique, and you now have all of that information available.

Note, as seen in the formulas above, that you will need absolute references to the cells that are the data table if you want to use AutoFill to copy the formulas down. This ensures that each formula looks in the same, correct range of cells.

Here is the result:

excel-vlookup-result
Result of Adding VLOOKUPs

Conclusion

VLOOKUP is a great time saver that allows you to lookup values in a cell in a table of data and pull another value from the same row that it found the data in. The resulting data can also be used in a PivotTable. The possibilities are nearly endless.

With the PowerPivot feature being added to Excel you have an option to VLOOKUPs. See this post to read more about PowerPivot and how to use it instead of VLOOKUP.

Let me know if you have any tips or questions related to using the VLOOKUP function.

Using Excel Forecast Feature

Excel forecast is a new feature with the ability to automatically create a forecast of your time based data. You can use this to forecast projected sales or costs for a variety of uses. It doesn’t even need to be financial data – you can use it to forecast anything that occurs over a time period.

When you use this new feature, Excel will automatically add a sheet with a forecast chart and a table showing the original data along with the forecast amount.

You will need two or more pieces of time/date data.

Below is a sample of nine months of data. Notice that there is no title for each column in the sample data. The Excel forecast will automatically add a title if there isn’t one, or will use the titles that are already there for each column.

excel 2016 forecast
Sample Forecast Data

Next, select the data series. You can click in only one cell of the data, as Excel will know to use all the data in the series. Then, under the forecast section of the data tab, click forecast sheet.

excel 2016 forecast
Excel Forecast Sheet Button

This will open up a form allowing you to change criteria before the forecast is automatically created.

You can preview the forecast chart, choose between a line chart and a column chart, change when the forecast will end, as well as several other options if you click the drop-down arrow beside ‘Options’.

excel 2016 forecast
Forecast Options

For the example data of nine months, the default forecast end was for another three months, bringing the total to one year.

If you leave all the options as default, and then clicking ‘Create’, Excel will add a new sheet to your document, placing a table and a chart as shown below to that newly added sheet.

excel 2016 forecast
Automatically Created Forecast Table
excel 2016 forecast
Automatically Created Forecast Chart

Creating a Custom Forecast

Forecast Start

You can change the month the forecast starts, by selecting months before the end of your actual data. Doing this lets you see how accurate the Excel forecast is compared what actually happened.

In the image below, the forecast month was moved from September to July. Notice that the forecast created for August and September was actually lower than what really happened. Ah, the perils of forecasting!

excel 2016 forecast
Effect of Moving the Forecast Start Date Back in Time

Confidence Interval

The confidence interval shows the possible range that the forecast could swing between, either higher than the forecast or lower.

This shows the effects of how confident you are that the forecast will be as predicted. This helps you decide how accurate the forecast could be.

A smaller percentage assumes more confidence in the forecast, and a larger percentage assumes less confidence in the forecast.

Shown below is the default 95% confidence interval, with a 25% confidence interval below it.

excel 2016 forecast
95% Confidence Interval
excel 2016 forecast
25% Confidence Interval

Seasonality

Represents the length of the seasonal pattern. The default setting is to automatically detect the pattern.

Timeline Range & Value Range

This is where you can change the range used. Both values need to match.

Fill Missing Points Using

The default is interpolation, which means that missing points will be the weighted average of its nearest points as long there is less than 30% missing.

Select zero from the drop-down list to replace them with zeros.

Aggregate Duplicates Using

If your data has more than one duplicate date value, Excel defaults to average the values. You can change it to do other functions instead of averaging here.

Include Forecast Statistics

Checking this box will add a table to the right of the data table created. This second table will show the measures used.

Conclusion

Excel’s new forecast feature really simplifies and visualizes the projecting and forecasting of values.

Try out this new Excel forecast tool, and create some amazing projections.

Let me know in the comments if you have any questions or ways that you have used this exciting new feature.

Simple Data Conversion to Excel Table

How many times have you started to use Excel to analyze some data given to you, and decided that you needed to filter it to summarize it quickly for you or for someone else.

Typically you would click in a cell within the data and then insert a filter. Excel is usually able to automatically add the filters to the correct columns.

Starting with Excel 2010, however, data can also be converted into an Excel table.

Why You Want a Table

By converting to a table, you get these benefits:

  1. Banded row colors are added
  2. Header row color changes to stand out from the data rows
  3. Header text is always visible as you scroll down through your data
  4. A data filter is automatically added
  5. Dynamic total row available
  6. Dynamic named range available for PivotTables

Creating a Table

Below is a sample of data before being converted to a table.

Table

To convert the data into an Excel table, click a cell within the data.

Then select Insert, and click Table. A form will pop up for you to select the range of data. The range defaults to what Excel think is correct. You can change the range of data if you want to.

If your data has headers, make sure to tick the check box. If you do not tick it, Excel creates its own header row with unique titles for each column, but will also include the first row as part of the data. This may not be what you intended.

Excel table

To convert the data to a table as shown below, click OK. The data can now be sorted or filtered.

Excel table

Column Headers Are Always Visible

If a table has more rows of data than the screen can show, the header names will always visible when scrolling down.

The column letters are replaced by the header text in the header, until you scroll back up to the top of table’s data. The screenshot below shows what happens.

Excel table

Automatically Calculated Columns

If you insert more columns in between columns already in the table, or a column directly the right of the table, that column then becomes part of the table of data. Also, any formulas entered in a cell in a column are automatically applied to all the cells in that column.

Total Row

A total row can be added to the bottom of the table. This can do summing, counting, averaging, and a variety of functions to columns in the Excel table.

Excel table